I would like to store many object types in a SQL database. Should I have a different table for each object type?

Question

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?

Answer 1

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.