6

The database stores different object types for engineering projects: motors, cables, pumps, sensors etc.

We're debating whether to have a different table for each object type? (Heaps of tables, a pain when we want to add a new object type -- which would happen every now and then...)

Or, as we currently do, should we have one table that stores the object types (ID, name) and another table that stores the possible attributes for each object type, and another table that stores the values of each attribute for each attribute type? (A real PITA, but flexible.)

Anyone done anything similar? Points to consider? Implementation?

wpearse
  • 2,422
  • 2
  • 29
  • 30
  • 3
    To me the latter sounds a bit like you're trying to build your own NoSQL database using a SQL database as storage. – Joachim Isaksson Mar 13 '13 at 21:13
  • 4
    Sounds like EAV vs Table Per Type. See [here](http://stackoverflow.com/questions/4066463/should-i-use-eav-model?lq=1) and [here](http://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce-question) –  Mar 13 '13 at 21:16
  • 1
    possible duplicate of [Product table, many kinds of product, each product has many parameters](http://stackoverflow.com/questions/695752/product-table-many-kinds-of-product-each-product-has-many-parameters) – Bill Karwin Mar 13 '13 at 21:19
  • How is your current system a PITA? – Dan Bracuk Mar 13 '13 at 21:48

1 Answers1

7

If you can get your hands on a copy of Patterns of Enterprise Application Architecture (Fowler) take a look at the Object-Relational Structural Patterns; there are pros and cons to each approach and the answer will be different depending on your particular project's context.

Specifically:

Single Table Inheritance

Class Table Inheritance

Concrete Table Inheritance

Serialized LOB (and if you consider this pattern also consider using a NoSQL datastore instead of an RDBMS)

The biggest question you'll need to answer is whether a relational database is the right data store for your data. Are you just looking for a place to store the data? Will you be using data from different objects in relation to one another? It might be enough to use a great serialization framework (like Kryo) into a Serialized LOB and store metadata necessary for searching or associating relationships in standard columns.

cfeduke
  • 23,100
  • 10
  • 61
  • 65
  • 2
    As always, a clear understanding of business requirements is a necessary prerequisite to making a sensible technology choice. – Pieter Geerkens Mar 13 '13 at 21:28