List:General Discussion« Previous MessageNext Message »
From:(S Date:March 1 2011 2:00pm
Subject:Re: Newbie question: Association table and Foreign Key
View as plain text  
>>>> 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.

Thread
Newbie question: Association table and Foreign KeyWagyu Beef1 Mar
  • Re: Newbie question: Association table and Foreign KeyDhaval Jaiswal1 Mar
    • Re: Newbie question: Association table and Foreign KeyWagyu Beef1 Mar
  • Re: Newbie question: Association table and Foreign Keyhsv2 Mar