Attributes of object with multiple types in one table

Question

I have a situation like this: I have multiple objects in one table and generally my columns depend on the type of object like crane, excavator etc.

My problem is that crane has other params than excavator so a few columns have null value. I thought I can do another table (attributes) and add a relation between attributes table and equipment table. Table attributes would have id attributeName attributeValue equipmentIdand perhaps type (to define type of value in attributeValue). Is it a good idea? Maybe exists other solution to this problem?

Answer 1

It depends on what you are doing with these tables.

I can imagine the following solutions:

1. Keep your current solution.

   This causes a messy table definition, and you may have to add more columns as new objects come in, but a NULL values doesn't take up any space in a PostgreSQL table.

   This is a good solution if you need to reference this table with a foreign key and want to have as few tables as possible.

2. Have one table that contains only the columns common to all objects, and for each object define a table with the additional columns. These tables have a UNIQUE foreign key to the table of all objects.

   This is similar to the first solution, but you end up with only the relevant columns.

   Your queries will have more joins, but you'll probably be faster if you want to access only objects of a certain type.

3. Have one table per object type and no common table.

   This will make queries that only access objects of one type simpler and faster, but you cannot have foreign keys that target all objects.

4. Have an empty table with the columns common to all objects, and have a table for each object type that inherits from the common table.

   This is a variant of 3., but enforces the common columns and makes it easier to write queries across all objects.

Answer 2

You are representing object data in a relational model.

Assuming that there are good reasons to do so, consider a m:n relation X that would relate equipment with attributes.

• X can have a instance attribute to specify (ID-ing) a particular object realisation with its attribute values.
• All values of an instance would go with a value column in X
  (possibly several columns to cater for different data types, though you may use a type system normalized to string representations like xml schema)
• The object type/class would go with the equipment table, where other attributes representing the inheritance relation(s) might be added, though it's likely that they merit their own relation (table), if needed.

Pro:

• Attributes may be shared between objects of different type/class.
• Attribute sets of different object types/classes do not need to be in a subset /superset relation

Con:

• Complexity