Design question

[Niniel]

Hello,

I'm trying to update this movie database. The original was mostly a flat db, so I'm trying to do things the right way this time.
Amongst other things, the db tracks if a movie is based on a book, and if so, what the title of the book is.
The text field [OrigBook] basically has three types of information - either it is empty, or it has the word "same" in it to indicate that book title and movie title are identical, or the real book title is listed.
Quite often it's "same", so I'm wondering how to model this correctly. I can't see how to do it any other way than it is right now, but maybe somebody here can give me some advice.

Thank you.

 

[CraigDolphin]

The text field [OrigBook] basically has three types of information - either it is empty, or it has the word "same" in it to indicate that book title and movie title are identical, or the real book title is listed.

Storing the three variants you've described in a text field in the movie table moght be ok. But consider: is it possible that a movie can be made based on more than one book? (Remember: Before Peter Jackson got the Lord of the Rings deal with New Line, Miramax wanted the trilogy to be condensed down into one movie)

Now, you might be happy enough to squish three book titles into the same text field depending on how you plan to use the information). Personally I'd not feel great about it. Then there's nulls where movies are not based on a book. They're something of a waste of space.

Then, what if you wanted to search for movies that are adapted from the same book as another movie (maybe to compare how each stays true to the book)?. How will you control for slight differences in spelling? What if one movie version uses the same name as the source book and another uses a different name? Which field would you use? At the least, your query would be convoluted.

My suggestion would be to create a new table tBooks and use a junction table to associate Movies with Books. That way, you can store many books with one movie, or many movies with one book, querying is a piece of cake, and you don't risk violating data normalization. It's a little more work setting up your forms, of course. So, if you never intend to ever use the book information except as eyeball fodder for a user, maybe you stick with your original design.

HTH

 

[Niniel]

Ah yes, Craig, excellent points. I didn't make the original db, so I'm not sure what the intent was with regards to the books. I suspect it was simply to provide another bit of information. But searching for movies based on books is a very good idea.
This was exactly the kind of advice I was looking for. Thank you.

Regarding Lotr though, one could just enter "Lotr trilogy".
Although in the case of the new Dune adaptations, the very issue you mentioned would arise.

 

[CraigDolphin]

Just to be argumentative...

Regarding Lotr though, one could just enter "Lotr trilogy".

One might. The next might enter 'Lord of the Rings: The Fellowship of the Ring; The Two Towers; The Return of the King' thus demonstrating my point.

Besides, I'm a rings junkie...any chance to mention it