Database model and performance

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

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.

I don't see any good reason to move those attributes out of the product table. It'd be one thing if you did it because you had some data that suggested a problem, but it looks like you thought "this will be better". Why did you do it this way right off the bat?

If you did it this way because it was generated for you, I'd recommend abandoning that generator.

People keep coming back to this model because they think it's "flexible". Well, it is I suppose, but that flexibility comes at a huge price: Every update and every query is slow and complex. Quassnoi mentions that if the attributes are sparse, i.e. most entity instances have only a small percentage of the possible attributes, this can save space. This is true, but the flip side is that if it is not sparse, this takes hugely more space, because now you have to store the attribute name or code for every attribute in addition to the value, plus you need to repeat some sort of key to identify the logical entity instance for every attribute.

The only time I can think of when this would be a good idea is if the list of attributes needs to be updatable on the fly, that is, a user needs to be able to decide to create a new attribute whenever he likes. But then what will the system do with this attribute? If you just want the user to be able to type it in and then later retrieve what he typed, easy enough. But will it affect processing in any way? Like, if the user decides to add a "clearance sale code", how will your program know how this affects the sale price? It could be done of course: You could have additional screens where the user enters data that somehow describes how each field affects pricing or re-ordering or whatever. But that would add yet more layers of complexity.

So my short answer is: Unless you have a very specialized requirement, don't do this. If you are trying to build a database describing items that you sell, with things like description and price and and quantity on hand, then create one table with fields like description and price and quantity on hand. Life is hard enough without going out of your way to make it harder.