I just spent some time discussing two alternative DB designs with a coworker and I'm not convinced. Neither of us is strictly DBA so we may be missing something.
The overall goal is to create the ability to attach open-ended free-text attributes for each of three (possibly 4) entities.
Let's call the business entities Device, Location and Part; they have relationships between themselves.
Design A: Create DeviceAttribute, LocationAttribute and PartAttribute tables, each of which has an ID, Reference ID (FK to respective table), Name, Value and Type.
Design B: Create Attribute table with (ID, Name,Value and Type columns) and three reference tables – each holding a reference from one of the entity tables to one of the Attribute tables' IDs.
The concern is mostly about performance:
– will 3 separate xxxAttribute tables perform better when querying only for an
Entity's data, such as "give me Device X and all its attributes", or
do both designs perform the same?
– will 1 Attribute table (Design B) perform better when querying for entities with given attribute name/value, such as "give me all entities that have Attribute.Name='GPS'", or is it equivalent to querying a view that combines the 3 tables of Design A?
– in case of Design B: does update on an entity table (Location, Device, Part) cause a lock on querying in other entity tables?
The system may have tens of thousands of Parts, thousands of Devices and hundreds of Locations and may have to process in the order of tens to few hundreds queries per second.
Best Answer
A couple of reasons I prefer a single table:
(1) with multiple tables you'll almost always be performing unions and you pay for the core data multiple times
(2) you can easily optimize for a subset of attributes or a combination of devices and attributes with indexes and use sparse columns to minimize wasted space
I wrote a bit about EAV here - it is by no means perfect, but we used it to solve many performance problems (at the cost of others) at a previous job.