Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Table design question

    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?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    How about this instead?
    Code:
    person_name  item_type  item_name
    -----------  ---------  ---------
    eric         book       book1
    eric         book       book2
    eric         song       song2
    ...

  3. #3
    Join Date
    Mar 2006
    Posts
    2

    thanks a lot.

    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
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •