Working with foreign tables

I consider this blog as an inspiration for others, not just as a promotion of QetriX ;-) so let me write a post about one of my former ideas for 3rd generation of my CMS (year 2007).

I was on a quest for automated database layer, so I came up with automated table joining without usage of "advanced" database features (designed for MyISAM engine).

From my former job I adopted a system of three-letter prefix for column names according to table name. The motivation for this was to have unique column names thruout the whole database schema. If I have table "users" (I use plural for table names), the prefix would be "usr", so primary key would be "usr_id". For PK in table "companies" it would be "com_id" and so on. If the table had similar name and the prefix was already taken, I'd use the next letter in order, so for "computers" along with "companies" it would be "com" and "cop" respectively.

For foreign keys I used even more specific approach, I used both table and prefix names from foreign table in the column name (plus "fk" suffix), so if user had relation to company, the column name in table "users" would be: "usr_companies_com_fk".

Thanks to this I was able to specify $db->getDataFrom("users") and it would return data from all joined tables as well. For this purpose I had cached database schema (like "information_schema.tables" in MySQL).

And that's it :)

Fun fact: The system was so automated, that when you deleted a row in one table, all referenced rows in all foreign tables has been deleted as well (incl. domains, users, locations etc). *palmface*

Pitching the idea

Like many developers, especially from my region, my crucial problem is to pitch my idea of the particle database. I crashed and burned many times before I realized I need to focus on just a few main features and don't try to explain the whole concept. But in this case, the main thing isn't that much exciting and everyone immediately sees i'm doing just an n-th copy of Freebase or something like that. Maybe I do, but not realized it yet...

However, I tried to sign up for startup event at local conference, but I didn't get their attention. At least the thought of I may get picked made me thinking more about what I do and how I can explain that to the crowd.

Then I tried to write few paragraphs about QetriX to local tech website's forum and got some responses. Fortunately all criticism was constructive and pushed me a leap forward. They mosťy pointed out that my UI is too complex and hard to understand, plus the database is quite empty. They got me thinking and ultimately I wrote a nice web crawler for semantic data extraction. I also redesigned the UI and dramatically reduced number of offered features, like comments or rating.

I slowly learned how to start simple and supress the urge to make everything perfect at once. Roll out is much more important than perfection. You can always add more features later. On the other hand, if you don't get user's attention in the beginning, you may not get a second chance from him. You have to determine your target group - geeks will demand features, but everyone else will demand simplicity. You can combine both, if you cleverly hide all the features and reveal them after the user is more experienced.

Q-Logo

Sun Microsystems

I like clever usage of name or at least a key letter in logo of product or company. My dream target was something like Sun Microsystems.

My requirements was quite clear:

• It should be stylized "Q",
• looks good (or at least is recognizible) as 16x16 px favicon,
• circle or square (not rectangular),
• bold lines, carvable for stickers,
• not too complex in vectors.

My minor requirements were to utilize golden ratio or be symmetric.

QeX (animated)

I felt little depleted and without invention, so I asked my younger brother, who does graphics and has good ideas, to help me. He offered me few designs and the first one gave me an idea to try invert and merge some colors. It has everything I expected.

I did some improvements, grooming and polishing, like determine width and thickness. The width would be 11 units, 1+3+3+3+1. The blue circle in the middle would have the same radius, as border of the outer blue rectangle; 1.5 units.

My brother's idea

My edit

Final version

The biggest surprise was when I tried to recreate it in SVG. I used Inkscape for edit, but the SVG output was too big. I tried to rewrite it in text editor. I found out the final layout will consits only from a single square, a single circle and a single line. I played with the blue as well. I really like the blue Google uses on focused input elements (#4D90FE), but it was too random for my taste. After some tries I ended up with nicer and web safe #6699CC (6-9-12).


I really like it, no matter I keep hearing it looks like a Pac-Man ;-) I see a harddisk, which is great icon for data platform.

I tried to modify it over the time, for example it still bugs me that I didn't use golden ratio (which is quite hard to achieve in concentric design :) or that I didn't used guide circles better. But so far it always ended by rollback.

The Name

In 2001 we started our Internet forum, called "QeX". Letters Q and X has been chosen by my friend Pavel, because those two are the only letters in Czech language, which you can't pronounce as a single letter.

Domain "qx.cz" was already taken, but I remember we saw it available when we looked for the first time couple weeks before. So we decided to put "e" in between (for better pronunciation, to make a word out of it) and, as a remembrance of the original idea, we kept "Q" and "X" uppercase.

In 2006 I discovered a leetspeak and at that time I was looking for available short ".net" domain, which serves better for Internet stuff. I tried to convert "qex" into leet and I got "q3x". I tried different pronunciations and then it hit me: "Q-tri-X" - sounds like "Matrix". So I registered q3x.net. I saved this name for my emerging project.

One day I tried various speech plugins in different languages, like Google Translate or Text-to-Speech in Windows, and I found out, that transcription as "qetrix" sounded quite the same in all major languages, which was my wish for project name. Since then I used "qetrix" much more often.

I always wanted a ".com" domain, because it's the most common TLD and it's somehow the best choice for a new startup. I already pronounced "q3x" as "qetrix", and to my delight (but not a surprise) "qetrix" was TLD-wide available. It took me just a few secs to decide and I claimed generous amount of TLDs :)

I kept q3x.net a primary and all qetrix.* redirected to it. Almost a year later I decided to use "qetrix.com" as primary and everything else as redirect.

And this way the word "QetriX" was born.