Database Design - Alternatives for Entity Attribute Value (EAV)

Question

see How to create a product table for many types of products, where each product has many parameters for a similar topic.

My question: I want to create a database that will be used for the production facility of various types of products, where each product has its own (number) of parameters. because I want the serial numbers to be in the same column for review purposes. I have a problem with these different parators.

One of the solutions may be EAV, but it has its drawbacks, of course, because we have + - 5 products with each product + - 20,000 serial numbers (records). it looks a little redundant to me ...

I just don’t know how to create a database so that you have an attribute in the master table that says: "Hey, you could find the details of this entry in the THAT detail-table." 'so that you can easily request results)

currenty I am using Visual Basic and Acces 2007. but I am moving on to Visual Basic and MySQL.

thank you for your help.

Bean

Answer 1

I would say 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

Answer 2

It seems you probably need to learn more about the available design patterns when solving such a problem, since there is no one size solution for everyone.

Enterprise Application Delvelopment, . , (, - , SO ), , , , , , .