Newbie question: Association table and Foreign Key
Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren
Re: Newbie question: Association table and Foreign Key
Just curious as it is not mentioned. Can Category ID also have multiple event id ? -- Cheers Dhaval Jaiswal On 01/03/2011 5:53 PM, Wagyu Beef wrote: Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren font Face='Arial' style='font-size:9pt'This e-mail, and any attachments are strictly confidential and may also contain legally privileged information. It is intended for the addressee(s) only. If you are not the intended recipient, please do not print, copy, store or act in reliance on the e-mail or any of its attachments. Instead, please notify the sender immediately and then delete the e-mail and any attachments. Unless expressly stated to the contrary, the views expressed in this e-mail are not necessarily the views of Enzen Global Solutions (P) Limited or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees makes no representation and accept no liability for the accuracy or completeness of this e-mail. You are responsible for maintaining your own virus protection and the Group Companies do not accept any liability for viruses. Enzen reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Enzen e-mail system./font -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: Association table and Foreign Key
Oh yeah, forgot to mention that. Yes, one event will have multiple categories. And one category can be applicable to multiple events. On Tue, Mar 1, 2011 at 8:33 PM, Dhaval Jaiswal jaiswal.dha...@enzenglobal.com wrote: Just curious as it is not mentioned. Can Category ID also have multiple event id ? -- Cheers Dhaval Jaiswal On 01/03/2011 5:53 PM, Wagyu Beef wrote: Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren font Face='Arial' style='font-size:9pt'This e-mail, and any attachments are strictly confidential and may also contain legally privileged information. It is intended for the addressee(s) only. If you are not the intended recipient, please do not print, copy, store or act in reliance on the e-mail or any of its attachments. Instead, please notify the sender immediately and then delete the e-mail and any attachments. Unless expressly stated to the contrary, the views expressed in this e-mail are not necessarily the views of Enzen Global Solutions (P) Limited or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees makes no representation and accept no liability for the accuracy or completeness of this e-mail. You are responsible for maintaining your own virus protection and the Group Companies do not accept any liability for viruses. Enzen reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Enzen e-mail system./font
Re: Newbie question: Association table and Foreign Key
2011/03/01 20:23 +0800, Wagyu Beef Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Well, if your problem is really like that in the example that you quote, then look up 'REFERENCES' under 'CREATE TABLE'. That shows you what to put in the common table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
