On Thu, Nov 27, 2008 at 1:36 PM, Fortuno, Adam <Adam.Fortuno@xxxxxxxxxxxxx>wrote: > Mr. Froasty, > > From your note, it sounds like you want to use foreign keys; as Daniel > pointed out. I think an example would be helpful here. The subject of > foreign keys is bigger than a bread box so I'll just touch on the pieces > I think you'll find helpful. There is all sorts of literature scattered > about the web if you want to know more. Let's start with a fictional > case: > > I work for a company with multiple departments each of which have one or > more employees. I would like a relational data structure to capture > departmental and employee information as well as preserve the > relationship between the two. > > Make sense? > > I create two tables: `Department` and `Employee`. Each table has a > primary key (as you illustrated in your example), which is unique per > record. <important>I add a column in Employee that holds the primary key > of the employee's associated department</important>. I then create a > relation between the two tables to indicate there is a relationship. > > --Create the Department table > CREATE TABLE Department ( > IDDepartment INT NOT NULL AUTO_INCREMENT, > Name VARCHAR(35), > PRIMARY KEY (IDDepartment) > ) ENGINE = InnoDB; > > --Create the Employee table and simultaneously the > --relation to Department > CREATE TABLE Employee ( > IDEmployee INT NOT NULL AUTO_INCREMENT, > idDepartment INT NOT NULL, > Name VARCHAR(35), > PRIMARY KEY (IDEmployee), > INDEX IDX_idDepartment (idDepartment), > FOREIGN KEY (idDepartment) REFERENCES Department(idDepartment) > ON DELETE CASCADE > ON UPDATE CASCADE > ) ENGINE = InnoDB; > > MySQL can do all of this provided you're using the InnoDB storage > engine. MySQL's documentation has some helpful information on the > subject - see link below. > > http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html > > With me so far? > > A few points specific to MySQL: > > (1) Whatever field you chose as your foreign key, needs an index. > (2) You can add foreign keys after a table has been created using an > ALTER statement. > (3) The option ON DELETE CASCADE means that whenever the parent record > (i.e., the department) is deleted the related employees will be deleted > too. > (4) The option ON UPDATE CASCADE means that whenver the parent's key > record (i.e., the department) is updated the related foreign key record > will be updated too. > (5) There are options other than ON UPDATE and ON DELETE. Give'm a look. > > Good luck, and welcome to the DB development club. > > Cheers, > Adam > > -----Original Message----- > From: mrfroasty [mailto:mrfroasty@xxxxxxxxx] > Sent: Thursday, November 27, 2008 5:19 AM > To: php-db@xxxxxxxxxxxxx > Subject: re:database tables relations advice > > I am quite new to database designs, I have a problem in my design...I > can actually feel it, but I am not quite sure if there is a feature in > mysql or I have to solve it with programming. > > Example: > CREATE TABLE A ( > user_id int(16) NOT NULL auto_increment, > ..........other datas > PRIMARY KEY (user_id) > ); > > CREATE TABLE B ( > user_id int(16) NOT NULL auto_increment, > ..............other datas > PRIMARY KEY (contact_id) > ); > > Question: > How can I declare that the user_id in my 1st table is related to user_id > in the 2nd table...actually I prefer to have it exactly the same user_id > in both tables....I think if those 2 entries are the same it will be > great, but I am not sure how to achieve this. > > P:S > -Ofcourse I know that I can extract it from TABLE A and save it in TABLE > B....but is that a way to go???Because this issue arise in couple of > tables in my data structure that I am tending to use in my > application(web). > -I also know that its possible to make just 1 big table with lots of > columns....but I read its not a good database design... > > ----->>>>>please advice, running out of ideas :-( > > Thanks...... > > > -- > Extra details: > OSS:Gentoo Linux-2.6.25-r8 > profile:x86 > Hardware:msi geforce 8600GT asus p5k-se > location:/home/muhsin > language(s):C/C++,VB,VHDL,bash > Typo:40WPM > url:http://mambo-tech.net > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > A couple of quick notes to add here: 1. MySQL supports FKs when using the INNODB engine, so you will need to change the engine type if the tables are anything else 2. It is possible to use the application to handle the keys instead of the database, it involves more work around key checking / validation before creating or updating records, but it can be done if the INNODB table type is not accessible to you for some reason. Inserting / reading from the table would be handled by an order precedence where you first select something from something like the users table to get the user id (or store it in session when logging the user in) and then adding that key to the other table. -- Bastien Cat, the other other white meat
