3

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.

Improve this question

3 Answers 3

Reset to default
7

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.

Improve this answer
2
  • Would performance be of a concern given the approximate throughput I expect?
    – user7609
    Sep 4, 2013 at 13:52
  • @user7609 way too many "it depends" to know. Just knowing the number of rows you're going to store isn't enough information to guide design - hardware, query patterns, concurrency, etc. are all factors you need to consider, and you simply need to test. Sorry.
    – Aaron Bertrand
    Sep 4, 2013 at 13:55
1

Design 'A' offers slightly higher performance for queries that don't require attributes from all 3 items (parts, locations, devices).
Design 'B' offers better extensibility and allows for a higher degree of complexity when writing queries, code, documentation, etc.

My tendency would be to use separate tables for each item type

Improve this answer
1
  • How big of performance gain would you guess? Would it justify the lower flexibility? As in - given the volumes listed in the end of my questions would design B slow things significantly or only theoretically?
    – user7609
    Sep 3, 2013 at 20:25
-1

EAV has been at the heart of many discussions but how about this design.

Table A holds the all entries relevant to the entities but the attributes. Table B holds the attributes with a FK and a TEXT field. In the TEXT field you can write any 'json' type data.

Obviously you can further optimize the tables.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.