SQL Database design with variant data types

Question

I'm starting a new project and I'm having some trouble finding the right design for the database.

The database domain have many entities that are similar to each other but have several distinct fields. This fields can change as the time goes by and many of them are enums. The objective is to design the database in a way that it's possible to control the fields and their possible values through an admin dashboard.

My idea is to have an entity Super entity that stores all the common fields between all entities, have an EntityCharacteristics which will store the entity caracteristics with foreign key to a Characteristic table which will store information about the characteristic that will make possible to create a form field with this information, imagine something like

CharacteristicName: Age
Placeholder: Enter your age...
InputType: text
CharacteristicType: Integer

It needs to be possible to store possible values for an InputType of selectbox for example which would be an enum like type. Also the SuperEntity would have a entity_type field which would be connected to an EntityType table and this table would be connected to a PossibleEntityCharacteristics which would store the possible characteristics to a certain entity type.

My problem is how should I store the values in EntityCharacteristics as they can be of different types, boolean, text, integer, enum etc.. and also how would I store the possible values in enum types, with Json? Or with another table of possible values for a certain characteristic? How would I be certain that a value inserted in EntityCharacteristics would be of the correct type and would contain a possible value for an enum?

Maybe this is bad design at all and I shouldn't be thinking like this and would just store the data in plain tables with many fields. But I would like to provide a solution that it is easy to change the existent fields and field values for the different similar entities at any time, and having to change the table schemas doesn't seem like a good idea for this. I plan to implement this with PostgreSQL which supports Json which may fit somewhere here, but as I never worked with this data type in Sql I dont know if it is a good idea.

I would like to know your opinion about this, and I thank you on advance.

Note: the database model I'm thinking is something like this https://stackoverflow.com/a/7423459 but a bit more complex and without the nesting.

Answer 1

This is a bit opinion-based, but anyway:

I would go for a single table that contains columns for all attributes that are common across all types of objects. Then have an additional JSONB column that stores the individual attributes that might vary between the different types.

You can take this a bit further and create a "type description" in a second table which defines the allowed attributes for a type. That can then be through the admin UI and you could also use it to verify the data that is put into the "dynamic" attributes of the base table.

Something like this:

create table object_type
(
  id integer primary key,
  name text not null,
  allowed_attributes jsonb not null
);  

create table objects
(
  id integer primary key,
  name text not null,
  object_type_id integer not null references object_type,
  attributes jsonb
);

insert into object_type (id, name, allowed_attributes)
values
(1, 'spaceship', '{"seats": "integer", "color": "text"}'::jsonb),
(2, 'book', '{"number_of_pages": "integer", "color": "text"}'::jsonb);

insert into objects (id, name, object_type_id, attributes)
values
(1, 'Heart Of Gold', 1, '{"seats": 4, "color": "white"}'),
(2, 'H2G2', 2, '{"number_of_pages": 42, "color": "black", "published_in": 1979}');

Now in the above example the published_in is an attribute that is not allowed based on the corresponding row in object_type. These rows can be identified with e.g. the following query:

select *
from (
  select *,
         attributes - (select array_agg(t.k) 
                       from object_type ot, jsonb_object_keys(ot.allowed_attributes) as t(k)
                       where ot.id = o.object_type_id) as invalid_attributes
  from objects o
) t
where invalid_attributes <> '{}';

You could even build a trigger that does this kind of checking when objects are inserted or updated.

Using the json_typeof() function you could also validate if the supplied value of a key matches the data type defined in object_type