Geek asks and answers

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

The database stores various types of objects for engineering projects: motors, cables, pumps, sensors, etc.

We are discussing whether to have a different table for each type of object? (Heaps of tables, pain, when we want to add a new type of object - what will happen from time to time ...)

Or, as at present, we must have one table in which the types of objects (ID, name) are stored and another table in which the possible attributes for each type of object are stored, and another table in which the values ​​of each attribute for each attribute type? (Real PITA, but flexible.)

Has anyone done something like this? Questions to consider? Implementation?

+5
source share
1 answer

If you can get a copy of Enterprise Application Architecture Templates (Fowler), see Object-Relational Structural Templates ; There are pros and cons to each approach, and the answer will be different depending on your specific project context.

In particular:

Inheritance of individual tables

Inheriting table classes

Concrete table inheritance

Serialized LOB (and if you are considering this template, also consider using NoSQL storage instead of RDBMS)

, , - - . ? ? (, Kryo) LOB , .

+6

More articles:


All Articles