Everything makes sense now

I was quite happy about the total reengineering. I started in a brand new directory and created brand new scripts. It was totally new approach, but the puzzle finally clicked together.

My first neglected todo was renaming the key table. There was no reason for calling it "a", it was historical reason when everything was put into "attribute" table. Because I came up with the name "particle" for the data structure and I still like single-letter name for tables, I renamed "a" to "p". As a result I was unable to use anything I created so far right away, I had to dip into everything. And it was a good thing.

I knew everything will be either a list, detail or edit form. In my opinion this realization was the second most important invention for QetriX, after particle. I was able to stick with these three components and create a compact code, as I always wanted. It would require much more discipline from me to keep everything in order.

Even QetriX is more about methodology, the idea without implementation is quite worthless. So I decided I'll rebuild the system in PHP again, maybe later in C# .NET or it will be my case-study for Java, Android, iOS, HTML5 or whatever... :)

I designed a new object model. I don't like bloated object apps with giant code overhead, mostly because I didn't use advantages of OOP, like inheritation. All my code uses KISS approach (Keep It Simple, Stupid), so I didn't feel the urge to organize my functions into static methods. But I knew if I want to make the code open source one day, I need to abandon my 1990s coding habits :)

I was going to create something like framework, so for a few weeks I studied some of the most used - Nette, Zend, CodeIgniter, CakePHP, Symfony, FatFree (lightweight counterpart), plus MediaWiki and Wordpress. Closest "to my heart" was CodeIgniter.

I created URL parser (routing) first. It will combine class-method approach with URL rewriter (for particular entity, loaded from database). I established a basic set of rules for certain URL patterns for determining what part of URL will be detected as class, method, entity ID and parameters. If no class is found, system asks database for entity URL and if no correspoding URL is found, HTTP 404 is fired. And if URL contains /data/, system searches for corresponding file to offer for download.

After quite long and sometimes quite frustrating debug it works like a charm. I'm still thinking about reducing some lines in code though, I'll be happy to omit file_exists checks for certain cases as this code is executed all the time. On the other hand without this part working I was unable to continue in development, so eventually I sacrificed few milliseconds for sturdy and reliable code.

New Destinations

I was going to try the engine on fleet management and dispatching system. It was pretty much the target usage, so I was particularly happy about it.

First showcase was a small fiasco, because future users didn't like it. It was too different from their current program, which was slowly dying for several unimportant reasons. The platform was (and still is) very flexible, so I promised to bend it as far as I could to meet their requirements.

It took me about 6 months of non-full-time development and the progress was tremendous. I started as "Win95" and ended as "Win7". It took so long because I stumbled upon some major problems of initial design and I had to reengineer, twice(!). But much worse was, that end users needed, but didn't want the new system. They were happy with status quo and didn't want to make anything better or different. In fact, they requested to recreate their current system (Win32 app) exactly as it was.

I don't want to go into much detail, as it's irrelevant for this story. I want to write about changes this experience led to.

The most notable change was a new approach to generating a form. Since then only controls for value has been generated. Now controls are generated for relation, order and flag as well.

For datetime control I created extremely rich/smart, but quite buggy (I must admit) control. It was single input (textbox), but thanks to a lot of JavaScript around the control it checked, what part of datetime are you currently on and offered appropriate functionality. Like when you wrote "8" on "day" part, it knew there are up to 31 days and "8" is the final day, so it skipped to "month" part automatically (in Czech Republic we use "d.m.yyyy" date format). On the other hand, if you wrote "2" on the day part, you stayd on the day part, because the control didn't know, if you wanted to write just "2", or e.g. "24". And there are more such things.
 

For number control I allowed user to change value using up-down arrows and because on Czech keyboard you have to press Shift to write number, I checked for the other without-Shift characters and convert them into appropriate numbers.

I also had to think more about more functionality, like "suggest" for values, drawing tables, forms, pages etc. I noticed some repeating patterns, but unfortunately I didn't have time for deeper analysis. But I kept it in mind.

The project finally didn't happened, even after I finished the last request and killed the last reported bug. It wasn't so big for the hassle, I already built an intranet for our company on it, which saves a lot of my coworkers' time. After I dropped this load off my shoulders, I was able to dust off the knowledge for the final reengineering. But this time absolutely from scratch.

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.

Filling the database

Alpha and Omega of website, like QetriX Particle Database, is data, data, data.

My primary goal was to import all countries of the world with major/capital cities and all settlements in the Czech Republic with all main attributes into the database. It was quite easy task and it filled about 60 000 rows (about 13 500 entities). I used mostly a spreadsheet (OOo Calc), because I downloaded and consolidated some Excel files.

Then I found list of all streets in Prague, all first and last names in the Czech Republic, I gathered list of all mobile phone models, some aircrafts and cars, a lot of series with episodes and finally I got a list of almost all airports in the world. Altogether it was about 400 000 rows.

I used custom made XML and TSV parsers. TSV is my favorite, because unlike commas, semicolons, pipes etc., tabs usually don't appear in data. Plus, when I copy-paste data thru clipboard from Calc to import front end, it's already as TSV - values separated by tabs. I had to create some custom made PHP scripts as well, for more complex or unevenly structured data sets.

One day I found some good keywords to find all kind of lists of Czech companies, so I put some of them into the database as well, along with some Czech POIs. But it was quite hard to find source of POIs without licence... There are great websites for POIs, but I don't steal. Anyway, 600 000 rows.

Then I found free to use sources of detailed structured data for particular aircrafts, like serial numbers, registrations, types, built dates etc. I love flying and I was always curious about the age of the aircraft I was about to embark. Not because I was scared, it was more about current state and what I can expect on board. Thanks to this I was able to create a simple app just for this :) 800 000 rows.

After that I had a lot of unfinished data sheets in Calc - taxonomy with animal species, administrative divisions and subdivisions of countries and cities, movies, songs etc. I was confident this is a good approach, because the data will be pure, complete to certain level and without any significant duplicities or mistakes. I also defended this position in some disputation about amount of data the system provides. Even I had some previous experience with web crawlers and data parsers, I didn't expect I'll have to use it here (rather I didn't want to use it here). Boy, I was wrong! :) The more I defended it, the more I thought about it and the more I understood this is the only way for larger number of data in database.

DB schema on a diet

OK, so I had 9 tables just for data, out of 18 in total. That's 9 different data structures programmer/code would have to deal with during data manipulations (CRUD). That's quite different from what I see as universal. It may be right from database development side, but ingenious ideas often come just because its author didn't know or care he's doing it wrong. And my layout was already quite wrong by storing numeric values as a text.

I decided to analyze table structure to find some similarities, allowing me to merge those similar tables. I was quite sure I'll find something in "type" tables, so I started with them. But it wasn't as easy as I thought. At the moment it was just primary key and name, but I already had in my head I'd like to store more specific stuff in each of them, like data validations, max length or value type (text, numeric, date...) for attribs.

What bothered me more though were those three m:n tables - because I couldn't do anything about them, as each of them was unique. The only way was to merge table for entities, attribs and relations into one, but they were just too different!

One day I talked about it with my best friend and former co-developer Pavel. I also pointed out the potential, which we looked for when we were creating QeX (more about it later). He encouraged me to go ahead and try to merge those tables. So I did. And the power of QetriX was born.

Semantic, baby!

OK, so I decided to create a semantic database. From relation database schema I had the basics - there's a row, row can have some columns and they may store some row's own values, or they can store a reference to some other row (in the same or different table). Years later I determined exact names I'll use for it: row = entity, column = attribute, reference = relation. And yes, any resemblance to ER model is purely intentional ;-)

My database model was quite obvious at this point: entity, attrib, relation, entity-attrib (m:n), entity-relation (m:n).

Then I started to think about what structure those tables should have. I started with "data" tables - entity, attrib and relation.

First two columns were quite obvious - integer primary key (id) + tinyint some kind of "type". Now I need something for data.

My first thought was: One column for text value + one for numeric value. And maybe one for datetime. Such model is good for performance and indexing, but I didn't like the code have to decide all the time where to put or look for a value and if I'd like to have one column always NULL. Single varchar column for value would be much better, but then I'll lose the performance advantage. But what about decimal numbers? Or dual values, like coordinates?

It felt like a Sophie's choice and after quite long thinking of all pros and cons I decided to sacrifice performance. I was going to store everything as a text, but the model will be pure.

Then I started to expand my thoughts about the whole schema little more. Relations should be able to contain attribs as well. So another table for m:n relation emerges. And all rels, ents and attrs would have their own type as well... there we go, another 3 tables. That's 9 now and that's it!

Those tables are just for data, nothing else. Sure, pretty much everything in QetriX is "data", but I needed more tables for specific purposes. Like translations, large text storage, secure file storage, activity log, change log, statistics, feedback or various caches. The schema grew big once again.