I am having trouble designing a schema in which there are two models an employee and projects. I am storing employees details in a table structure as:  id | employee | report_to   wherein employee table store the employee id and report_to store the id of the employee to whom the employee reports.  I have another table in which we store diff projects created by employees.  Id | project_name | employee_id   Now if I need to get projects done by employees who report to me I can easily get the list and also get the projects.  But the concern is eventually over time the employee reporting manager changes and hence along with that all the project that falls under let's say project manager 'a' also changes.   How to handle this kind of a problem in database. can this be designed in such a manner so that the projects which were initiated during that time remains under that particular manager only?
121571
What is the cleanest way to capture changes between levels in a dimensional hierarchy?  I have the dimensional hierarchy [Area] > [Region] > [Location], where Area is the parent to Region and Region is the parent to Location. Location is lowest level of the hierarchy, and is associated with the fact table.  The business rules are: Locations are able to change the Region they are assigned to based on business need. Furthermore, Regions are able to change the Area they are assigned to.  We want to capture those changes so that when we do historical analysis, we can compare business metrics before and after a Location changed its Region, and a before and after a Region changed its Area.  What is the best way to do this?  The four ways that I have come up with all seem to have major drawbacks:   Using a bridge table between hierarchy levels requires that you use time bounds (effective start/end dates) to correctly identify which parent a child belongs to as you walk up the hierarchy. That makes queries prone to error, especially with hierarchies with many levels. Versioning a child record whenever its relationship to its parent changes (e.g., versioning Region A when it moves from Area A to Area B) and then cascading that change all the way down the hierarchy (e.g., creating new versions of Locations tied to Region A so that they are tied to the new Region A). Updating the child record's reference to the parent record, making you use time bounds to reassemble the correct history (prone to error). Always use time bounds when querying up the hierarchy to make sure Location A is associated with the correct Region (likewise, Region A is associated with the correct Area) for the moment of the fact being analyzed.   None of these seems clean to me.  Is there a better way?  Thanks a lot
24956
I wonder if there is the ability to print / export the schema builder?  If so, where can I find? If not, would be a great idea to implement this feature for future releases.
29474
I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change.  As an minimal example, suppose I want to list the leaders of various countries in a database that tracks many politicians, including those in positions other than 'leader'. I can create a table of politicians, and a table of countries, like so:  CREATE TABLE politician (   id integer unsigned NOT NULL,   name varchar(45) NOT NULL,   PRIMARY KEY (id) );  CREATE TABLE country (   id integer unsigned NOT NULL,   name varchar(45) NOT NULL,   PRIMARY KEY (id) );   Now, if every country had only a single leader forever, I could just link these with a table such as:  CREATE TABLE country_leader (   countryid integer unsigned NOT NULL,   leaderid integer unsigned NOT NULL,   PRIMARY KEY (countryid, leaderid),   KEY fk_countryleader_country_idx (countryid),   KEY fk_countryleader_politician_idx (leaderid),   CONSTRAINT fk_countryleader_country FOREIGN KEY (countryid) REFERENCES country (id) ON DELETE NO ACTION ON UPDATE NO ACTION,   CONSTRAINT fk_countryleader_politician FOREIGN KEY (leaderid) REFERENCES politician (id) ON DELETE NO ACTION ON UPDATE NO ACTION );   However, this situation obviously isn't the case, because existing leaders are sometimes re-elected (possibly even with some time between two different terms in office), and when they aren't then new leaders take over. So I think that a better link table would look something like this:  CREATE TABLE country_leader (   countryid integer unsigned NOT NULL,   leaderid integer unsigned NOT NULL,   termstart date NOT NULL,   termend date NOT NULL,   PRIMARY KEY (???),   CONSTRAINT ???,   .... );   So my question is, what is the best way to fill in the missing PK and constraint information in the junction table country_leader? Or should I be using a different approach to solve this problem?
86473
I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id)  My question is, is it possible if the project_id and user_id's data is different  like project_id = '12345' and user_id = 'abc987'  Please reply  kind regards Vivek
121398
Suppose I have a table containing job roles:  CREATE TABLE roles (   "role" character varying(80) NOT NULL,   CONSTRAINT "role" PRIMARY KEY (role) );   Suppose I further have a table, users, and each row (a specific user) can have an arbitrary number of job roles:  CREATE TABLE users (   username character varying(12) NOT NULL,   roles character varying(80)[] NOT NULL,   CONSTRAINT username PRIMARY KEY (username) );   I should probably make sure that each member of users.roles[] exists in roles.role.  It seems to me that what I want is a foreign key constraint on each member of users.roles[] such that if references roles.role.  This doesn't seem possible with postgres.  Am I looking at this the wrong way?  What is the suggested "right" way to handle this?
68568
I'm working on a personal project where I want to make a database that holds the information for every owner of every public company. Say for example "Sears INC" and the program would get the information for every owner in "Sears INC". That is the idea. But I am having a really hard time trying to structure this database. I only have limited experience and knowledge about this subject so any guidance would be greatly appreciated.   Now I was first thinking about making a table called companies, and give them all unique ID's and make a table for every company. Then inside those tables would be all the owners with their unique ID. That would link to their information. I have tried to visualize this here:    Now I know that creating a table for every company would be tedious but I cant think of any other way of doing this. As you all know each company might have the same owner and therefore it would make sense of using Owner_ID to identify each owner.   I have all the other data in a CVS file that I could easily import in PostgreSQL after I have structured the database.  Any help would be greatly appreciated.   TL:DR; Creating a database of stock owners that will be searchable by company name, and need help to structure the database for maximum efficacy.   A person can only have one phone and one address. Same goes for the company, because I have made a script that finds the phone based on this information. The way I differentiate between knowing if a entity is a company or person is by using a column called F_Org which will be larger than 4 digits for companies. This is all handled by the script.
79492
I have four columns. All columns are varchar fields. Three of them are independent and distinct fields having many to many relation between them and the fourth one is dependent on the combination of first three. so for all the combinations of three columns there is different value for the fourth column. Now I want a design for data like this. Any help is appreciated.
39502
I'm building a support ticket tracking app and have a few models I'd like to create from one page. Tickets belong to a Customer via a ForeignKey. Notes belong to Tickets via a ForeignKey as well. I'd like to have the option of selecting a Customer (that's a whole separate project) OR creating a new Customer, then creating a Ticket and finally creating a Note assigned to the new ticket.  Since I'm fairly new to Django, I tend to work iteratively, trying out new features each time. I've played with ModelForms but I want to hide some of the fields and do some complex validation. It seems like the level of control I'm looking for either requires formsets or doing everything by hand, complete with a tedious, hand-coded template page, which I'm trying to avoid.  Is there some lovely feature I'm missing? Does someone have a good reference or example for using formsets? I spent a whole weekend on the API docs for them and I'm still clueless. Is it a design issue if I break down and hand-code everything?
91645
I am working on a conceptual model for a database. I have the following entities:  Student (id, name, date of birth, department)  Subject (id, name)   This is a many-to-many relationship, one student may be enrolled in many courses, each course has many students. From what I understand, as I should break this many-to-many relationship by introducing a new into a entity, say 'registered' which allows a one-to-one relationship or one-to-many relationship?  Is this correct design decision?
33480
At work we have a big database with unique indexes instead of primary keys and all works fine.  I'm designing new database for a new project and I have a dilemma:  In DB theory, primary key is fundamental element, that's OK, but in REAL projects what are advantages and disadvantages of both?  What do you use in projects?  EDIT: ...and what about primary keys and replication on MS SQL server?
97745
I have a database setup with the following schema:   CREATE TABLE siteInfo (     siteID             SERIAL  NOT NULL,     siteName           TEXT    NOT NULL,     siteHost           TEXT    NOT NULL,     notes              TEXT    NOT NULL,     allowableCountries TEXT    NOT NULL,     displayChance      INT     NOT NULL,     imagePath          TEXT    NOT NULL,     PRIMARY KEY (siteID) );  CREATE TABLE siteLinks (     siteID             SERIAL  NOT NULL,     URL                TEXT    NOT NULL,     FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE );  CREATE TABLE siteDemographicLinks (     siteID             SERIAL  NOT NULL,     URL                TEXT    NOT NULL,     country            TEXT    NOT NULL,     chance             INT     NOT NULL,     FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE );  CREATE TABLE linkVisits (     siteID             SERIAL  NOT NULL,     rawVisit           BIGINT  NOT NULL,     redirects          BIGINT  NOT NULL,     FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE );   Data from my form is in the following format:  array(7) { ["siteName"]=&gt;string(9) "Site Name" ["siteHost"]=&gt;string(9) "SIte Host" ["notes"]=&gt;string(5) "Notes" ["links"]=&gt;array(2) {[0]=&gt;string(6) "Link 1" [1]=&gt;string(6) "Link 2"} ["countries"]=&gt;string(4) "None" ["displayChance"]=&gt;string(2) "15" ["extraLink"]=&gt;array(2) {    [0]=&gt;array(3) {       ["url"]=&gt;string(7) "dSite 1"       ["countries"]=&gt;string(4) "NONE"       ["chance"]=&gt;string(2) "11"       }    [1]=&gt;array(3) {       ["url"]=&gt;string(6) "dSite2"       ["countries"]=&gt;string(4) "None"       ["chance"]=&gt;string(2) "13"       }    } }   How would I go abound inserting data into the many type tables so they reference tuples inserted into the one type table?  I have this query constructed:  INSERT INTO siteInfo (siteName, siteHost, notes, allowableCountries, displayChance, imagePath) VALUES ('Sample', 'sample.com', 'note', 'NONE', '50', '../var');   I would then need to insert data into the siteLinks table, which is related to the entry already in the siteInfo table.  INSERT INTO siteLinks (siteID, URL) VALUES ((SELECT siteid FROM siteinfo WHERE sitename='Sample'), 'test');   Is there a better way of doing this?
24424
I'm testing some queries with MySQL and tried to create two tables were there should be a reference between IDs. Something like:  CREATE TABLE user  (   id int(11) UNSIGNED AUTO_INCREMENT,   name varchar(30) NOT NULL,   PRIMARY KEY id );  CREATE TABLE photo (   id int(11) UNSIGNED AUTO_INCREMENT,   user_id int(11) UNSIGNED,   filename varchar(50) NOT NULL,   PRIMARY KEY id );   A query like:  SELECT p.filename, u.name FROM user AS u INNER JOIN photo AS p ON u.id = p.user_id   Would result in all records of photos and their user's name.   So if it is possible to create a relation on query, why would I use foreign keys and define NO ACTION, considering I want to keep a history of photos, if there is no need for that? Foreign keys helps on performance or just make data more organized?
123542
I'm considering using Django for a project I'm starting (fyi, a browser-based game) and one of the features I'm liking the most is using syncdb to automatically create the database tables based on the Django models I define (a feature that I can't seem to find in any other framework). I was already thinking this was too good to be true when I saw this in the :  Syncdb will not alter existing tables syncdb will only create tables for models which have not yet been installed. It will never issue ALTER TABLE statements to match changes made to a model class after installation. Changes to model classes and database schemas often involve some form of ambiguity and, in those cases, Django would have to guess at the correct changes to make. There is a risk that critical data would be lost in the process. If you have made changes to a model and wish to alter the database tables to match, use the sql command to display the new SQL structure and compare that to your existing table schema to work out the changes.  It seems that altering existing tables will have to be done &quot;by hand&quot;. What I would like to know is the best way to do this. Two solutions come to mind:  As the documentation suggests, make the changes manually in the DB; Do a backup of the database, wipe it, create the tables again (with syncdb, since now it's creating the tables from scratch) and import the backed-up data (this might take too long if the database is big)  Any ideas?
72822
NOTE The audience of programmers.se and dba.se is different, and will have different viewpoints, so in this instance I think it's valid to duplicate  on programmers.se.  I couldn't find discussion on dba on this already, and the original post says it all, so:     Most software developers want to keep   application logic in the application   layer, and it probably feels natural   for us to keep it here. Database   developers seem to want to put   application logic in the database   layer, as triggers and stored   procedures.      Personally I would prefer to keep as   much as possible in the application   layer to make it easier to debug and   keep the responsibilities of the   layers separate.      What are your thoughts on this, and   what should or should not be ok to   implement in the database layer?   N.B. I'm not the OP for that question, but left the original wording intact.
105471
I know this has been asked  and , but I have the same idea with a different possible implementation and I need some help.  Initially I had my blogstories table with this structure:  | Column    | Type        | Description                                    | |-----------|-------------|------------------------------------------------| | uid       | varchar(15) | 15 characters unique generated id              | | title     | varchar(60) | story title                                    | | content   | longtext    | story content                                  | | author    | varchar(10) | id of the user that originally wrote the story | | timestamp | int         | integer generated with microtime()             |   After I decided I wanted to implement some versioning system for every story on the blog, the first thing that came to my mind was creating a different table to hold edits; after that, I thought I could modify the existing table to hold versions instead of edits. This is the structure that came to my mind:  | Column        | Type          | Description                                       | |------------   |-------------  |------------------------------------------------   | | story_id      | varchar(15)   | 15 characters unique generated id                 | | version_id    | varchar(5)    | 5 characters unique generated id                  | | editor_id     | varchar(10)   | id of the user that commited                      | | author_id     | varchar(10)   | id of the user that originally wrote the story    | | timestamp     | int           | integer generated with microtime()                | | title         | varchar(60)   | current story title                               | | content       | longtext      | current story text                                | | coverimg      | varchar(20)   | cover image name                                  |   The reasons why I came here:   The uid field of the initial table was UNIQUE in the table. Now, the story_id is not unique anymore. How should I deal with that? (I thought I could address story_id = x and then find the latest version, but that seems very resource consuming, so please give your advice) author_id field value is repeating in each every row of the table. Where and how should I keep it?     Edit  The unique codes generation process is in the CreateUniqueCode function:  trait UIDFactory {   public function CryptoRand(int $min, int $max): int {     $range = $max - $min;     if ($range &lt; 1) return $min;     $log = ceil(log($range, 2));     $bytes = (int) ($log / 8) + 1;     $bits = (int) $log + 1;     $filter = (int) (1 &lt;&lt; $bits) - 1;     do {         $rnd = hexdec(bin2hex(openssl_random_pseudo_bytes($bytes)));         $rnd = $rnd &amp; $filter;     } while ($rnd &gt;= $range);     return $min + $rnd;   }   public function CreateUID(int $length): string {     $token = "";     $codeAlphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";     $codeAlphabet.= "abcdefghijklmnopqrstuvwxyz";     $codeAlphabet.= "0123456789";     $max = strlen($codeAlphabet) - 1;     for ($i=0; $i &lt; $length; $i++) {         $token .= $codeAlphabet[$this-&gt;CryptoRand(0, $max)];     }     return $token;   } }   The code is written in , and was originally written in PHP by  in his .  The fields author_id and editor_id can be different, because there are users with enough permissions to edit anyone's stories.
59811
Here is the script to create my tables:   CREATE TABLE clients (    client_i INT(11),    PRIMARY KEY (client_id) ); CREATE TABLE projects (    project_id INT(11) UNSIGNED,    client_id INT(11) UNSIGNED,    PRIMARY KEY (project_id) ); CREATE TABLE posts (    post_id INT(11) UNSIGNED,    project_id INT(11) UNSIGNED,    PRIMARY KEY (post_id) );   In my PHP code, when deleting a client, I want to delete all projects posts:  DELETE  FROM posts INNER JOIN projects ON projects.project_id = posts.project_id WHERE projects.client_id = :client_id;   The posts table does not have a foreign key client_id, only project_id. I want to delete the posts in projects that have the passed client_id.  This is not working right now because no posts are deleted.
28828
I am pretty well versed with SQL Server, MySQL, Oracle etc but putting these Database products aside, is there a resource that will help me design relational databases well? Is there something like patterns or best practices for database design?  I have seen a few times that database is often not scalable; people have personal preferences with keeping columns like isChecked column which is boolean in nature but stored as Char(1) with values like 'Y' and 'N' instead of 0 and 1 which to me sounds better. Ways not to commit common mistakes while doing database design?  Links to books or articles will be highly appreciated.  Thanks in advance.