[Dbix-class] Schema design query

[Dermot]

2009/2/27 Marcello Romani <mromani at ottotecnica.com>:
> Dermot ha scritto:
>>
>> 2009/2/27 Rob Kinyon <rob.kinyon at gmail.com>:
>>>
>>> This requirement would have a cds table, a genres table, and a
>>> many-to-many join table. That join table would have ( cd_id, genre_id
>>> ). Look up many_to_many as a relationship.
>>>
>>> Rob
>>
>> I think the penny might have dropped.
>>
>> The join table has, as you say, 2 columns (cd_id and genre_id).
>> However neither column is set as UNIQUE so you can have multiple
>> instances of cd_id thereby a single CD might appear more than once,
>> creating the many_to_many. Cool.
>
> In think you should set UNIQUE(cd_id, genre_id).

Am I missing something? If the cd_id field is UNIQUE then, unless I'm
mistaken, there can only ever be one instance of a CD in the join
table so I wouldn't be able to represent that one CD belongs to many
genre's. I thought I wanted:

cd_genres
1|1
1|4
2|1
3|4
3|5

CD_1 belongs to genre_id 1 and 4; CD_2 just to genre_id 1; CD_3 to
genre_id 4 and 5. I think a has_many accessor on the CDs.

I'm not sure if it makes a difference but I am talking about 300,000
CDs being mapped to 7 genres.
Thanx,
Dp.