Re: [PHP-DB] re:database tables relations advice
On Fri, Nov 28, 2008 at 5:52 PM, Fergus Gibson [EMAIL PROTECTED] wrote: On Thu, Nov 27, 2008 at 11:19 AM, Bastien Koert [EMAIL PROTECTED] wrote: 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. Yes, but generally foreign key constraints are preferable. The database server is compiled in native code, so it'll be much faster at handling this issue; and it may avoid the necessity of your script submitting multiple queries to resolve the foreign key issues. A bigger issue though is that the implementation of foreign key constraints on the server will have been exhaustively tested. Reinventing the wheel in your own PHP code may introduce bugs that cause loss or damage to your data. Correct, I was merely offering an alternative if the INNODB / FK constraints are not available. I have to do that with ASP code that I maintain, but its a real bitch to manage -- Bastien Cat, the other other white meat
Re: [PHP-DB] re:database tables relations advice
On Thu, Nov 27, 2008 at 11:19 AM, Bastien Koert [EMAIL PROTECTED] wrote: 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. Yes, but generally foreign key constraints are preferable. The database server is compiled in native code, so it'll be much faster at handling this issue; and it may avoid the necessity of your script submitting multiple queries to resolve the foreign key issues. A bigger issue though is that the implementation of foreign key constraints on the server will have been exhaustively tested. Reinventing the wheel in your own PHP code may introduce bugs that cause loss or damage to your data. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] re:database tables relations advice
This is solved by using FOREIGN KEY but I'm not sure if MySQL have them present or just planned for some future release. mrfroasty napsal(a): 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 tablesI 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 Bbut 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 columnsbut I read its not a good database design... -please advice, running out of ideas :-( Thanks.. -- S pozdravem Daniel Tlach Freelance webdeveloper Email: [EMAIL PROTECTED] ICQ: 160914875 MSN: [EMAIL PROTECTED] Jabber: [EMAIL PROTECTED]
Re: [PHP-DB] re:database tables relations advice
maruti wrote: hey lemme knoe, how many columns are you planning for? y not put table B columns in table A?? if you are least bothered about data normalizations, simply go the way you want to. userId Name Age Age Sex Occupation Location all the data which has correspondence with userid can be put in a single table.. the case is if you dont've many related tables.. pavan puligandla Microsoft has no beef with OpenSource. 2008/11/27 mrfroasty [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] maruti wrote: hii,, who said table with more no:of columns is a bad database design? coming to your tables, what do you wanna do?? if you want to pull out the data of 'X' userid from table B, then you can use left join. make sure that user ID of table A and userID of table B have same data. to use joins, atleast one column(s) should be the same in both tables. let me know whether userID of table B is a foreign key of UserID of table A or not? normalization in all cases doesnt work. i'm using spreadsheet as my front end, so my tables are not even in the second normal form.. here are some excellent tutorials for joins; http://www.tutorialspoint.com/mysql/mysql-using-joins.htm http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html hope these might help u alot.. pavan puligandla Microsoft has no beef with OpenSource. 2008/11/27 mrfroasty [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 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 tablesI 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 Bbut 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 columnsbut 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 Quote: make sure that user ID of table A and userID of table B have same data. #That is actually what I am looking for, but I dunno how to achieve that.If I can have those 2 user_id columns with the same data, my problem is technically solved.But as I said earlier I am limited to database skills, its my first application that involves mysql phpdo I need those foreign key stuffs, to have those 2 columns with the same data?? -- Extra details: OSS:Gentoo Linux-2.6.25-r8 profile:x86 Hardware:msi geforce 8600GT asus p5k-sehave location:/home/muhsin language(s):C/C++,VB,VHDL,bash Typo:40WPM url:http://mambo-tech.net That issue arise like 3 times in my data structurehaving all related data in one table I might ended up with a table of ~20 columns, it will be too much and probably bad programming practise.I will have a look over the net about FOREIGN KEYdidnt know what it is, may be its the solution... Thanks for the input -- 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:
Re: [PHP-DB] re:database tables relations advice {solved}
danaketh wrote: This is solved by using FOREIGN KEY but I'm not sure if MySQL have them present or just planned for some future release. mrfroasty napsal(a): 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 tablesI 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 Bbut 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 columnsbut I read its not a good database design... -please advice, running out of ideas :-( Thanks.. -- S pozdravem Daniel Tlach Freelance webdeveloper Email: [EMAIL PROTECTED] ICQ: 160914875 MSN: [EMAIL PROTECTED] Jabber: [EMAIL PROTECTED] Thanks for the input...after some small research I came across this link http://articles.techrepublic.com.com/5100-10878_11-6035435.html after that I changed my database to something similar to : CREATE TABLE user_profile ( user_id int(16) NOT NULL auto_increment, other datas PRIMARY KEY (user_id) )ENGINE=INNODB; CREATE TABLE user_contact ( user_id int(16) NOT NULL auto_increment, ..other datas INDEX (user_id), FOREIGN KEY (user_id) REFERENCES user_profile (user_id), PRIMARY KEY (user_id) ) ENGINE=INNODB; That I think problem solved for now...thanks alot :-) -- 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
RE: [PHP-DB] re:database tables relations advice
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. importantI 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:[EMAIL PROTECTED] Sent: Thursday, November 27, 2008 5:19 AM To: php-db@lists.php.net Subject: [PHP-DB] 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 tablesI 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 Bbut 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 columnsbut 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
Re: [PHP-DB] re:database tables relations advice
On Thu, Nov 27, 2008 at 1:36 PM, Fortuno, Adam [EMAIL PROTECTED]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. importantI 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:[EMAIL PROTECTED] Sent: Thursday, November 27, 2008 5:19 AM To: php-db@lists.php.net Subject: [PHP-DB] 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 tablesI 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 Bbut 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 columnsbut 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
