[thelist] SQL Madness! Many-to-many selection

[rudy]

> would the FK fields individually be a better index
> than a single joint index with the two, or would a
> combination of all three indexes work the best?

that all depends  ;o)

take for example the two parent tables

   event ( event_id, event_stuff
       , primary key event_id )

   category ( category_id, category_stuff
       , primary key category_id )

now, when you build the relationship table, do you go with the primary key
as the concatenation of the foreign keys like this

  eventcategory  ( event_id, category_id, eventcategory_stuff
       , primary key (event_id
                              , category_id)
       , foreign key (event_id) references event
       , foreign key (category_id) references category )

or do you give this table a surrogate key

  eventcategory  ( eventcategory_id
       , event_id, category_id, eventcategory_stuff
       , primary key (eventcategory_id)
       , foreign key (event_id) references event
       , foreign key (category_id) references category )

obviously, if an event can belong to the same category *more than once*
then you need the second instance, no getting around it

real world example?  people and projects

a person can be on a project more than once (even at the same time) by
having different roles, e.g. designer, coder -- role would be one of the
eventcategory_stuff columns...

(this example, if you stop and think about it for a sec, is a
many-to-many-to-many relationship!! but i digress...)


my main criterion for what makes a "better" design is first and foremost a
design that accurately fits the information that needs to be stored

aside:  this pretty much always means 3rd normal form, sometimes higher


so if you need multiple occurrences of the concatenated foreign keys in the
relationship table, then of course you need a surrogate primary key, dig?


as for indexes, the rule of thumb for relationship tables is that each
foreign key gets a separate index, you wouldn't have an index on the pair
of them

primary keys, of course, always get their own index


helps?


rudy