Database model and performance

Question

I'm using a self constructed database model. This model is constructed for a webshop application. this is how it looks like:

First of all I have a table for my products. This contains only general data like id and articlenr, for all of the product attributes (like name, price,etc) I have made seperate tables per type, so I have the following tables :

product_att_varchar
product_att_decimal
product_att_int
product_att_select
product_att_text
product_att_date

these tables are related by a relational table procuct_att_relational

My problem is the performance of this structure, if I want all the attributes of a specific product if have to use so much joins that it will slow down very much.

Does anyone have a solution for this???

Thanks

Answer 1

This model is called EAV (entity-attribute-value) and has its drawbacks and benefits.

Benefits are that it's very flexible and can be extended easily. It may be useful if you have very large number of very sparse attributes, the attributes cannot be predicted at design time (say, user-provided), or the attributes that are rarely used.

The drawbacks are performance and inability to index several attributes at the same time. However, if your database system allows indexed views (like SQL Server) or clustered storage of multiple tables (like Oracle), then by using these techniques performance can be improved.

However, storing all attributes in one record will still be faster.