I am utilizing a self built database model. This model is built for any webshop application. this is the way it appears like:
To begin with I've got a table for my items. This consists of only general data like id and articlenr, its the merchandise characteristics (like title, cost,etc) I've made seperate tables per type, and so i possess the following tables :
product_att_varchar product_att_decimal product_att_int product_att_select product_att_text product_att_date
these tables are associated with a relational table procuct_att_relational
My issue is the performance of the structure, basically want all of the characteristics of the specific product if need to use a lot joins that it'll decelerate greatly.
Does anybody possess a solution with this???
This model is known as
EAV (entity-attribute-value) and it has its disadvantages and benefits.
Benefits are it's very flexible and may be extended easily. It might be helpful for those who have very many very sparse characteristics, the characteristics can't be predicted at design time (say, user-provided), or even the characteristics which are rarely used.
The disadvantages are performance and lack of ability to index several characteristics simultaneously. However, in case your database system enables indexed sights (like
SQL Server) or clustered storage of multiple tables (like
Oracle), then by utilizing they performance could be enhanced.
However, storing all characteristics in a single record it's still faster.
I do not use whatever valid reason to maneuver individuals characteristics from the product table. It would be one factor should you made it happen since you had some data that recommended an issue, however it appears like you thought "this is better". Why did you're doing so by doing this quickly the softball bat?
Should you made it happen by doing this since it was produced for you personally, I'd recommend abandoning that generator.
People keep returning for this model simply because they think it's "flexible". Well, it's I guess, but that versatility comes in a huge cost: Every update and each totally slow and sophisticated. Quassnoi mentions when the characteristics are sparse, i.e. most entity instances have only a tiny proportion from the possible characteristics, this could save space. This is correct, however the switch side is the fact that if it's not sparse, this takes greatly more room, because now you must to keep the attribute title or code for each attribute additionally towards the value, plus you have to repeat some kind of answer to identify the logical entity instance for each attribute.
The only real time I'm able to think about when this is advisable is that if their email list of characteristics must be updatable quickly, that's, a person must have the ability to decide to produce a new attribute whenever he likes. However what's going to the machine use this attribute? Should you simply want the consumer to have the ability to types in after which later retrieve what he typed, simple. And can it affect processing by any means? Like, when the user decides to include a "clearance purchase code", the way your program understand how this affects the purchase cost? It may be done obviously: You might have additional screens in which the user makes its way into data that in some way describes how each area affects prices or re-ordering or whatever. But that will add yet more layers of complexity.
So my short response is: Unless of course you've got a very specialized requirement, avoid this. If you're attempting to develop a database explaining products that you simply sell, with such things as description and cost and and quantity available, then create one table with fields like description and cost and quantity available. Existence is difficult enough without heading out of the way to really make it harder.