Birth of QetriX

So I was going to create an ultimate database model and try the second option, mentioned in one of my previous blog posts. Now I was skilled enough to see much further ahead and avoid any performance issues in advance.

Thanks to the decision I reduced the model from 18 tables to 6. But I'm getting ahead of myself.

I was fairly familiar with parent-child approach, utilizing hierarchical database model, so I decided to use it. Each row has "parent_fk" column; attribs and relations will point to entity's primary key there and if the parent_fk is the same, asi primary key, it's an entity.

For value I defined "Twitter's" varcahr(140) column / domain. Longer values would go to a different table, when needed.

Then it was time to deal with relations. My first idea was to put foreign key into the varchar column for attrib value, but it was no-go even for my anarchistic side, not mentioning it would give many invalid relations (with numeric values) and sooo many invalid JOINs (esp. with text values). Adding new numeric column to the table was inevitable, but later I realized it was a pivotal moment.

From some experiments emerged it would be useful to add order and flag/strenght/significance. I tried to combine it into single numeric column, but it wasn't safe and might collide in some cases. To keep the model compact I didn't want to add two columns, so I made a Solomon's decision and created decimal column, where integer part would be significance and fractional part order.

In MySQL I used FLOAT for this column, but later I found out, when I use a datetime in numeric format (yyyymmddhhmmss) as order, the precision isn't sufficient and the number gets crooked. After some testing I redesigned the datatype as DOUBLE(18,14). I figured I wouldn't need more than 4 digits for significance.

After I finished the "data" table, I moved to "type" table. Design of its structure was just about what I expected from the system. I had parent type, type and format od value (text, number, url, isbn, date, zip...), order in entity, max allowed occurencies in entity, units (for converting), max length of value etc.

A saw a major flaw - entities can't share the same attribute type, each entity type allows only own attribs. This led to a new table, but this one was fairly useful, so I didn't mind. After all, it was the third one and I made i optional.

I used this table for multiple purposes. Except for reason mentioned above, after I added a column for a relation, I was able to define a list of entity types, to only which the particular relation can point.

My employer gave me a golden opportunity to use my data platform on a real project, which was a fleet management system, and I was ready to take the challenge.