Table design question

yueliu_32214 · #1 (**permalink**) 03-22-06, 19:01

Hi,
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?

andrewst · #2 (**permalink**) 03-23-06, 07:12

How about this instead?

Code:

person_name  item_type  item_name
-----------  ---------  ---------
eric         book       book1
eric         book       book2
eric         song       song2
...

yueliu_32214 · #3 (**permalink**) 03-23-06, 11:32

I can't believe why I didn't think of this.

Quote:

Originally Posted by andrewst

How about this instead?

Code:

person_name  item_type  item_name
-----------  ---------  ---------
eric         book       book1
eric         book       book2
eric         song       song2
...

Internet Services

Web Development

Digital Marketing

Consumer Tech