High-level DB schema design question

[cbeggan]

Hi,

I am looking at a DB redesign where I can identify two options for storing data records. In the design, there is a major need for flexibility.

The data comes in a number of types (ie. some records contain many different fields than others). An example might be a store catalogue that contains books and CDs. Both have title/author info, but CDs have track info and lyrics while books have ISBNs and reprint dates . In reality there will be many many book/cd type divisions. The options seem as follows:

1/ Use a core table for common data (e.g. title, price, genre) and link it to specific tables for specific product type ( a CD track info table, book codes table etc)

2/ Link the aforementioned core table to a single key-value table with generic columns and generic datatypes
e.g
id type item data
123 cd track01 'Fur Elise'
123 cd producer 'Brian Eno'
124 book isbn '1231231123'
124 book editor 'John Doe'
125 cd track01 'Drive'
123 cd track02 '1812 Overture'
124 book year '1998'

Is this a way to go? Has anyone seen it working in a sizable application environment? The second way is very flexible (no need for new tables if we start selling shoes for example) but I can see queries becoming quite cumbersome on this type of table (e.g books published in 1998, edited by john doe, etc, requires a self-join for every single extra parameter in a 'where' clause...)

Anyone got any references/experience/advice on this? I am thinking method 2/ is more flexible and require less DBA time, but will get nasty when scaled - leaving coders with complex and potentially slow queries - and the queries will all be hitting the same table...

Advice appreciated....

c.
------
Dublin, Ireland.

 

[pelajhia]

Here is a long but very good explanation of why you probably want to go with your Method #1, commonly referred to as 'Normalization'. Please read the article because it goes into detail that should make your whole lay out of the db go very smoothly.
Good luck!

http://www.devshed.com/Server_Side/MySQL/Normal/Normal1/page4.html

(this links to the middle of the article, about what you're most interested in, but there are some previous pages to it, also, all worth a read)

 

[bitsinc]

How about using a distinct table for each entity. Then you can join diverse objects/info together with queries. If you only need one entity type, which I would think is usually the case, your query is straightforward.

Between the two you've presented, option #1 is a better design in my mind - your query for any entity type could be unwieldy.