A "strict" relational database doesn't suit this task well because you're left with a choice of:
- Different tables for each object type with a columns for each attribute that applies to that particular object type
- A single table for all object types, with columns for each attribute, most of which aren't used for any given object type
- A child table, one row for each attribute
Before moving on to a good general solution., let's discuss these:
1. Different tables for each object type
This is a non-starter. The problems are:
- high maintenance cost: you must create a new table every time you add a new object type to your app
- painful queries: you must join to every table, either horizontally - every table joined into one enormously long row, or vertically in a series unioned joins, leading to a sparse array (see option 2)
2. A single table for all object types
Although you're dealing with a sparse array, if most object types use most of the attributes (ie it's not that sparse), this is a good option. However, if the number of different attributes across your domain is high, and/or most attributes aren't used by all types, you have to add columns when introducing a new type, which although better than adding tables, still requires a schema change for a new type = high maintenance
3. A child table
This is the classic approach, but the worse to work with, because you either have to run a separate query to collect all the attributes for each object (slow, high maintenance), or write separate queries for each object type, joining to the child table once for each attribute to flatten out the many rows into one row for each object, effectively resulting in option 1, but with an even higher maintenance cost writing the queries
None of those are great options. What you want is:
- One row per object
- Simple queries
- Simple schema
- Low maintenance
A document database, such as Elasticsearch gives you all of this out of the box, but you can achieve the same effect with a relational database by relaxing "strictness" and saving the whole object as json in a single column:
create table object (
id int, -- typically auto incrementing
-- FK to parent - see below
json text -- store object as json
);
BTW, postgres would be a good choice, because it has native support for json via the json datatype.
I have used this several times in my career, always successfully. I added a column for the object class type (in a java context):
create table object (
id int,
-- FK to parent - see below
class_name text,
json text
);
and used a json library to deserialize the json using the specified class into an object of that class. Whatever language you're using will have a way of achieving this idea.
As for the hierarchy, a relational database does this well. From the canvas:
create table canvas (
id int,
-- various attributes
);
If objects are not reused:
create table object (
id int,
canvas_id int not null references canvas,
class_name text,
json text,
layer int not null
);
If objects are reused:
If objects are not reused:
create table object (
id int,
class_name text,
json text
);
create table canvas_object (
canvas_id int not null references canvas,
object_id int not null references object,
layer int not null
);
