Hello Stackoverflow comunity!
In my opinion that my question has related to all db guru here!
Are you aware the EAV DB Plan ( http://en.wikipedia.org/wiki/Entity-attribute-value_model ) and the things they say concerning the carrying out of the model.
I question, Basically break this model into more compact tables what it makes sense?
Let us discuss it.
I've got a db with increased that 100K records. Lots of groups and several products ( with various qualities per category ) Things are saved inside a EAV.
Basically attempt to break this plan and make for just about any category a distinctive table is one thing which will I must avoid?
Yes, I understand that most likely I'll have lots of tables and I'll have to ALTER them if I wish to add an additional area, BUT is so wrong?
I've also read that as numerous tables I've, the db is going to be populate with increased files which is not great for any filesystem.
Because the primary structure inside a database design, the dwelling will fail because the data develops. How you realize that a database schema doesn't fit the business design is when you really need to question against it for confirming. EAV's require many workarounds and non-native database functionality to be able to get reasonable reviews. I.e., you're constantly creating crosstabs/pivot queries for the littlest query. Everything processing to accept EAV and set it inside a queryable format chews through CPU cycles and it is highly vulnerable to error. Additionally, how big the information keeps growing geometrically. For those who have 10 characteristics, 10 rows inside a standard design will generate 100 EAV rows. 100 standard rows would associate to 1000 EAV rows and so forth.
Database management systems are made to handle plenty of tables which should not be any worry.
You'll be able to produce a hybrid solution where an EAV structure is part from the solution. However, the rule should be that you could never incorporate a query
[AttributeCol] = 'Attribute'. I.e., you cant ever filter on, sort on, restrict the number on any attribute. You can't place a particular attribute any place in a study or on-screeen. It's really a blob of information. Combined with a decent schema for that relaxation from the system, getting an EAV that stores a blob of information could be helpful. The important thing to succeeding is enforcement among yourself and also the designers not to mix the road of blocking or sorting with an attribute. When you go lower the dark path, forever does it dominate your future.
EAV DB schema is extremely flexible for adding more relational database's "posts" but at the expense of going down hill the query performance and losing your company logic that was stored within the relational database schema.
Because you need to create multiple sights to really pivot the end result, that will make the performance problem when the table consists of vast amounts of rows. And the other character of EAV schemas is queries will always be made whenever you join the information table using the meta data table and there can be multiple joins on a single data table.
This is dependant on my experience.
You will find database engines purpose created to run EAV models. I'm not sure them and so i can't recommend one. But pushing an EAV model right into a relational engine is really a occur. Disaster will occur, it is only a matter of time.
It is possible that the data will remain sufficiently small, as well as your queries not so difficult with this to operate but that is rarely the situation.