MySQL Forums
Forum List  »  Newbie

Table design questions?
Posted by: Eric Zhang
Date: March 22, 2006 05:55PM

I have a question about table design, it is not specifically related to MySQL.
The scenario is very simple: A person can own books, movies, songs etc.
Option 1 : Just one single table

person_name books songs movies
eric book1
eric book2
eric song1
eric movie1
eric movie2
eric movie3
Terry book1
Terry song1
Terry song2
For this option, you can see, a lot of "NULL" have to be stored in the table
Attention: The post mess up the format, the book name should lineup with field
"books", sonngs should line up with field "songs", etc.

Option 2: Create as many table as necessary for books, songs, movies, etc.
table: books
person_name book
eric book1
eric book2
terry book1

table: songs
person_name songs
eric song1
eric song2
terry song1

table: movies
person_name movie
eric movie1
eric movie2
terry movie1

As you see, in this option, no "NULL" need to be stored. However, you have a lot
of tables. Also, it will require union and other multi-table operations for some
search, which is not needed by the option1.

Which option is better?


Options: ReplyQuote

Written By
Table design questions?
March 22, 2006 05:55PM
March 23, 2006 05:48AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.