Storing layered objects in a grid

Question

Let's say I have a canvas where there are various objects I can add in, such as a/an:

• Drawing
• Image
• Chart
• Note
• Table

For each object I need to store the dimensions and the layer order, for example something like this:

• ObjectID
• LayerIndex
• Dimensions ((x1, y1), (x2, y2))

Each of the objects have vastly different properties and so are stored in different tables (or classes or whatever). Would it be possible to store this into a relational database, and if so, how could it be done? In JSON it would be something like this:

// LayerIndex is the ArrayIndex
// No need to store ObjectID, since the object is stored within the array itself
Layers = [
    {Type: Drawing, Props: <DrawingPropertyObj>, Dimensions: [(1,2), (3,4)]},
    {Type: Chart,   Props: <ChartPropertyObj>,   Dimensions: [(3,4), (10,4)]},
    {Type: Table,   Props: <TablePropertyObj>,   Dimensions: [(10,20), (30,44)]},
    ...
]

The one option I thought of is storing a FK to each table, but in that case, I could potentially join this to N different tables for each object type, so if there are 100 object types, ...

Answer 1

A "strict" relational database doesn't suit this task well because you're left with a choice of:

1. Different tables for each object type with a columns for each attribute that applies to that particular object type
2. A single table for all object types, with columns for each attribute, most of which aren't used for any given object type
3. 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
);