Newbie question: Association table and Foreign Key

--0016e64bc18cc10b77049d6ade05
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e64bc18cc10b77049d6ade05--

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
>
>
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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Newbie question: Association table and Foreign Key

--0016e64bc18c542c44049d6b1f94
Content-Type: text/plain; charset=ISO-8859-1

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.dhaval@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
>>
>>
>>
> 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.
>

--0016e64bc18c542c44049d6b1f94--

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=gcdmg-mysql-2@m.gmane.org