Database design - alternatives for Entity Attribute Value (EAV)

Question

Hi,

see http://stackoverflow.com/questions/695752/product-table-many-kinds-of-product-each-product-has-many-parameters for similar topic.

My question: i want to design a database, that will be used for a production facility of different types of products where each product has its own (number of) parameters. because i want the serial numbers to be in one tabel for overview purposes i have a problem with these different paraeters .

One solution could be EAV, but it has its downsides, certainly because we have +- 5 products with every product +- 20.000 serial numbers (records). it looks a bit overkill to me...

I just don't know how one could design a database so that you have an attribute in a mastertable that says: 'hey, you could find details of this record in THAT detail-table". 'in a way that you qould easely query the results)

currenty i am using Visual Basic & Acces 2007. but i'm going to Visual Basic & MySQL.

thanks for your help.

Bob

Answer 1

It seems you probably need to learn more about the available design patterns when dealing with this sort of problem as there isn't a one-size-fits-all solution.

I recommend picking up a copy of Patterns of Enterprise Application Delvelopment to help you on your way. Sorry that I'm not able to answer your question directly (hopefully someone else here on SO can) but I think the answer given in the question you linked to is about as good as it gets.

Answer 2

I would go with something like this:

product [productid, title, price, datecreated, datemodified, etc]
attribute [attributeid, title]
productattribute [productid, attributeid, value, unit]

Example:

[product]
productid  title    price   datecreated  datemodified
1          LCD TV   99.95   2010-01-01   2010-01-01
2          Car      12356   2010-01-01   2010-01-02
3          B/W TV   12.95   1960-01-01   1960-01-01

[attribute]
attributeid  title
10           Colors
11           Dimensions
12           Passengers

[productattribute]
productid  attributeid  value  unit
1          10           16     million
1          11           32     inch
2          12           4      adults
3          10           2      colors
3          11           6      inch