Database design - alternatives for Entity Attribute Value (EAV)

Question

see How to design a product table for many kinds of product where 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

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