View Single Post
Old 12-01-2007, 11:38 PM   #7 (permalink)
Dr John
The Contributor
Join Date: Apr 2005
Location: Kent, UK
Posts: 54
Thanks: 0
Dr John is on a distinguished road

Yes, that's the sort of thing I was trying to get at. A look-up table is for convenience and usually only has the one attribute (it doesn't need an artificial ID, the genre were unique to begin with). But as no other attribute had a functional dependancy on genre, there was no real reason to remove it to another table, UNTIL the multi-valued part was considered (matrix, action) and (matrix, sci-fi), then it required the link table to list each movie and genre pair. Which was why I included that bit.

I personally always think in terms of entities, as I seem to be much faster at normalising things that way. A movie can belong to many genres. A genre can apply to many movies. A many to many relationship - normalise, and add link table.

Suppose a movie could only belong to one genre - a one to one relationship.
Use a look-up table for convenience, if you wish to. (But in this case, you'd end up with some awkward genres eg comedy-action-japanese-sc-fi, then you'd realise a movie did NOT belong to just one genre and spot the many to many, as searching for comedy should return this movie, and you've gone multi-valued, not allowed in relatiional DBs.

I've noticed it is very common for people to add an ID to a table when one of the attributes is already a unique identifier. When you add ID to genre you end up with three superkeys - id, genre and (id,genre), of which two are candidate keys - id, and genre, and you choose either of the candidate keys to be the primary key. But id was added as if there was no natural simple candidate key, when genre did that job by itself. So the id in the genre table was instantly unnecessary.

Hope this helps a bit more. As I said, over simplifaction of examples masks what is actually being done and why.
Dr John is offline  
Reply With Quote