Using the Database Designer UI. A short intro to the functionality.

Using the Database Designer

We'd like to present you with our most stable release of the Database Designer. It's usually quite similar to other database web-based management tools you'd expect from PHP programmers, but this one comes with a major twist. 35years+ of experience from one database hacker/scientist. (evil laugh!)

So without further dues, let me introduce the major features that resides on its surface, because quite frankly, that's where it should stand apart.

This is a simple snapshot of the database tables that more or less consists in the Blogs. (which is used to build this article you're reading actually.) We can readily identify that the table cryptochocolate.Blog seems to sit in the middle of a web, the arrows represent actual SQL relationships that are (manually or automatically) mapped between the tables. The arrow should always point to the parent object of a relationship. (This differs somewhat from regular basic SQL theory, where you can have bidirectional arrows, but we'll come back later to the reasons of our system).

All the objects in this Designer are clickable of course, and right-clickable for contextual options. Each table has in its header a number of somewhat useful links to further details about the table. And this is where we diverge from the industry. The DDL link will take you to a sub-standard DDL editing screen (it's sub-standard in our system for now because it is not the primary focus, just a helper to figure things out.). The U.I. link is where the magic starts to happen, this will take you to a U.I. Editing screen which allows you to document each field for their presentation in the User Interfaces of the system(s). The third link; View Data takes you to the Listing data page of that table, and the last link Add a Record takes you to the record creation screen for that table.

Intro to the table User Interface generation

So, what's the U.I. got to do with database tables? In a corporate setting, I personally have found this methodology very useful, exceptionally under tight time-constraints for producing prototypes and even working production code. If you want to know, the author is an expert guru of quick code crunching, and like they say, the most lazy people make for the best programmers (not that I'm lazy, but with my todo list I lack a couple of lifetimes. So throughout my career I've always been looking to make the best of my time, using and developing my own shortcuts.) But, enough with my reasons for combining User Interfaces on top of a Database Management system. :p Let's move on to the usefulness of such a system.

For starters, we have to understand how this engine does its 5th generation magic on the database tables. The engine attempts to conceptualize the working relationships found between the tables (usually declared in your database schema) to build a framework of user interfaces (see; an Extranet, Internet or Intranet website) using the contained tables.

In order to do this efficiently, tables are "wrapped" with a logic object which takes care of analyzing, reverse-engineering the database table, and then, tacking on a user-defined template on top of the table to generate a user interface specific to his written/spoken language. For those who know X10-X12, the database could be in X10 format, and the user-defined interfaces would map to the field names defined in the standards. (Which is one particular corporate use that can be very handy).

Here's a quick notional graphic of how this works; keep in mind that it's overly simplistic because we're progressively learning about the system.

As you can surmise, the A) element corresponds to a database table in its SQL (well, DDL) descriptive format, once we reach step B) the engine has done its analysis and has retrieved the user-defined overlay which corresponds to finally arrive at step C) where we actually generate the said Interfaces. In the engine we do generate each of these interfaces independently (code-wise, they are very different beasts), but rest assured, the engine is object-based and is entirely focused on speed optimizations.

It does get a bit more complicated for our casual readers, once we consider the parent-child relationships and the fact that our engine will also generate a parent's associated (in a SQL-relative way) child records. There are constraints to this tree structure building, and the user is totally free to disable/enable child renderings in his settings.

So, back to our Designer...

Creating and Modifying relationships

Let's first look at the simplest of all relationships in a database model, a Many-to-One relationship. In our engine we commonly refer to such a relationship as a Parent-Child relation, because a Parent can have many Children, and many Children can belong to the same Parent. (That's classical SQL theory)

So, in this picture, we see that the cryptochocolate.Blog table is related to the cryptochocolate.SYS_Site table. (In the base installation of the engine itself, these tables are used extensively. SYS_Site corresponds to defined websites which we use to figure out weblinks most importantly, and the Blogs, well... you're reading them.)

We can see that the arrow is pointing from Blog to SYS_Site, meaning that Blogs are Child to the SYS_Site Parent. This signifies in practice that a Blog cannot be posted to more than 1 website at a time (let's call this a useful constraint for now, Google doesn't like it when people replicate Blog content across different websites anyways, you'd get penalized on the SEO scale.). The arrow is further labeled with a resume of the relationship information, we note;

the relation name (usually multiline, sorry about that, but TMI issues)
the fields on which the relation is built, in this case Site_Name being the foreign-key to primary-key in the corresponding tables
whether or not deletes should cascade from parent to child (ie; if the engine deletes the associated child items when the parent is deleted)
whether or not the child U.I.s should popup
whether or not the child should be rendered when rendering its related parent
whether or not the relationship should be taken into account when packaging the model (more on this later.)

To edit a configured relationship, simply left-click on it. You'll get to an interface which resembles this:

The quick ones will notice that this corresponds to the resume information contained in the relation's label. But with more information on what each option means. Of course! (We've been busy making the engine more self-service with the hopes of making a total no-brainer for beginners to design databases, more on that later.)

So, as the options are pretty self-explanatory at this point, we'll skip to the bottom part where we can adjust the keying between 2 tables. Depending on the application that you're currently using, these relationships will probably have been already defined, and thus, we'd warn you against liberally fiddling with them. But when creating new relationships, the engine will NOT currently reverse-engineer the tables to figure the optimum fields to use, and there's a pretty good reason for that.

In our engine, we step outside of the regular DBM world because the engine concerns the marriage of data and user-interfaces, rather than being data-centric. So, in our keying system, we can define special operators; such as smaller-than, greater-than, not equal, equal, etc.. (depending on where we're at with the current feature set), and we can also override the right-hand side with a manual value. (This is another useful constraint of the system, its logic, it makes sense, you'll see.) Right now this Blog to Site is a poor example for such a special relation, but we didn't want to lose our readers in technicalities for now. Relations are limited to 4 fields; another useful constraint.

You might have noticed the overflowing dropdown for choosing a rendering type, this is still an experimental feature (but it works, just not ironed out entirely), which allows to tune the type of interfaces that should be rendered when including the child user interfaces into the parent interface. A useful one is the proxy-grabbag, which we'll be able to look at as we turn to the Blog Categories.

Adding / Deleting relations

This one deserves it own chapter, yes. To "draw" a new relationship, suffice to click on one of the orange square markers (which identify primary keys) and drag to the table we which to relate to as a child. So, the action goes; click on the table to be a Child, and drag from the square to the Parent table of your choice. If you mess up and do it in reverse, don't fret. You'll see it right away, the engine will render a simple pointed arrow to indicate the directionally.

You can delete a relationship by right-clicking on it with your mouse. ;)

When adding a new relationship, it's key fields will be blank (and the relationship won't be saved to the database yet). In order to finalize your relationship, click on the arrow or its label, and align the fields that should be used as keys. The engine will automatically pickup the fieldnames from the database through a remote call.

The Blog Categories relationship, introduction to Many-to-Many relations

So, in SQL relationship theory, we have 3 different kind of relationships; One-to-One, One-to-Many and Many-to-Many. Of these 3 relationships we say that we can transform and manipulate the relational aspects of our databases to normally reduce or rationalize our conceptual models into practical models, or better yet, in order to promote sane work. :) Well.. that's probably not the right explanation for it, but that's how I've always interpreted in my mind.

The theory goes that a Many-to-Many relationship can be reduced to a set of 3 tables using 2x One-to-Many relationships. And our Blog Categories is the purrfect example for this. Why would we have to to the extra legwork? Well, because it makes our lookups way more efficient, and allows us, in this case specifically, to abstract Category naming from their associations with different blogs. (So a Blog can be part of many categories, and a category can contain many blogs. Many-to-Many.)

But when we render user interfaces (or do SQL maths), we'll typically resort to a more practical model such as the following;

So we clearly see this relationship:  Blog <- Blog_Categories -> Blog_Category

Blog_Categories (yes, plural in this case) is composed of strictly 2 foreign keys which make up its primary key, and that's the only thing this table holds. It could hold more, useful fields such as a creation time, or a status flag so we could track category joinings and removals in time. But our system (at this time) doesn't, things are kept simple.

Clicking on the label between Blog and Blog_Categories, we obtain this relationship editing screen;

What is of interest is the rendering type for the children which in this case is configured as a proxy relationship. This means that when we render the Blog object in its user interfaces, we'll use this relationship to define a sub-listing of objects to be presented along with it. This translates to a listing of categories to which a Blog editor can add to, create new Blog_Category data rows (because its being proxied) or delete/modify Blog_Categories at will.

We'll see more details on how the engine parses our user preferences to render the different interfaces in the following blogs.

So, after making changes to any relationship, you should definitely click Apply (instead of Close, we should look into disabling that button when changes are made.. ermf..), and the relationship in itself will get saved to the database (apart from any visual changes, like moving objects around in your drawing). We do this because managing relationships is an entirely different game than drawing a nice layout. :D

Right-clicking, right!

"Right" - Brett, Alien

In the designer, you can pretty much right-click anything, except for the freaking background (very useless constraint, alas, haven't figured a way around that one yet.) So, just keep in mind that if you want to right-click & save your canvas, you need to right-click a table, any table will do. While we're at it, I think you'll find the contextual menu self-explicit, you can also Add a table to your canvas, anytime. The engine is sufficiently aware to not let you re-add the same table twice (this one is another useful constraint). Because at this point in time we didn't want to introduce the complexity of managing components that were off-canvas (relationships would have to be handled differently, when pointing to objects that were out-of-scope). So for the moment, the Canvas is the whole of your system.

It's not necessary to add every table to the visual designer, usually when generating a new web application stack the engine will render all the tables it finds that were already defined. (This is a trick we use to package and rollout new systems too.)