Database design, wish list and its items.

Question

I am creating a new database to manage wish lists.

Solution A:

Two tables,

• wish_list and
• wish_list_item

Solution B:

One table,

• wish_list_with_item

  This would have a wish list item per column, so it will be many columns on this table.

Which is better?

Answer 1

Solution A is better. Anytime you try to store collections in columns rather than rows, you're going to run into problems.

Answer 2

I would definitly go with the first solution :

• at least, it means you can have as many items you want -- and it'll probably be easier to deal with, on the application side (think about "deleting an item", or "adding an item", for instance)
• the second solution absolutly doesn't feel right

Answer 3

solution A is normalized and solution B is not. In most situations solution A will be better and more flexible. The major time this is not true is if you are making a summary table of some complex join on large tables for use as a source of quick queries for common questions. Unless you are building a datamart, this is unlikely to be the case. Go with solution A.

Answer 4

The second solution would be better only if you are forced to do a denormalization for the sake of easier caching or if your application grows immensely and you need database sharding. In all other cases I prefer a cleaner design of the database.