Data Modeling with Alzabo

Now that we have installed Alzabo, let's create a simple database schema using the browser-based design tool. Admittedly this is not as slick as commercial or client-side tools, but it does the job rather well.

Begin by creating a new schema (known in PostgreSQL and MySQL parlance as a database) to which you must give a name. The schema must be a legitimate database name within either PostgreSQL or MySQL. I choose to work with PostgreSQL because of its built-in referential integrity, foreign keys, views and triggers, as well as a more standard dialect of SQL and the ability to write stored procedures in a variety of languages.

Let's create a simple phone book and appointment calendar using Alzabo. We will keep track of people we know, their addresses and telephone numbers, and appointments we have scheduled with them. Using this database, we can learn about the people with whom we're meeting on a given day or about all of the appointments with a given person.

To create this schema, we point our web browser at the URL alzabo/schema under the Mason directory we mentioned earlier (on my computer, I pointed the browser to http://localhost/mason/alzabo/schema.) This brings up the schema creation/editing page that allows us to edit an existing schema, create a new one or reverse-engineer an existing one. While the last option is the most interesting, allowing you to access legacy databases using Alzabo, we will create a new schema. I entered the name (I chose addressbook, for lack of a better idea) and indicated that we wish to use PostgreSQL as our back-end database.

After clicking on “submit”, several possibilities were presented: I could add a new table to this schema, delete the entire schema or examine the SQL that Alzabo will generate automatically. Right now, of course, there isn't any SQL to display. Over time, we will see this SQL grow considerably.

However, because Alzabo has not created any SQL doesn't mean that no work has been done on the back end. Indeed, Alzabo automatically created the addressbook directory within /usr/local/alzabo/schemas, containing three files: addressbook.create.alz and addressbook.runtime.alz (both are stored in a binary format) and addressbook.rdbms, which contains the single word PostgreSQL. In this way, Alzabo tracks the database server in which the schema is stored.

Once inside the addressbook schema, I added a “People” table by entering “People” in the “add a table” text field and clicking on “submit”. (PostgreSQL ignores case in table and column names, but I like Joe Celko's convention of initial caps for Table Names, all lowercase for column names and all caps for SQL RESERVED WORDS.)

Within my People table, I created columns, each of a different data type. Alzabo offers a menu of potential data types, but we can enter our own if we want; this can be particularly useful in PostgreSQL, which allows us to create our own data types.

I generally prefer to work with synthetic primary keys in such a table, giving each row its own value. In PostgreSQL, we accomplish this using the SERIAL data type. But you will notice that no such data type exists in the Alzabo selection list. You might be tempted to indicate that this is an INTEGER column and to mark the “sequenced” check box at the bottom of the column editor. Doing so, however, will create an INTEGER column, as well as a totally unrelated PostgreSQL sequence object. Rather, to get a synthetic primary key you must manually enter SERIAL in the text field below the <select> list of column types.

An additional check box lets you indicate if a column is the primary key and automatically marks it with “pk” in column listings. And a third check box allows you to indicate if a column may contain NULL values, a subtle way of reminding new database designers that NULLs complicate life and should be avoided whenever possible.

To create a foreign key (REFERENCES) or CHECK clause, add it in the “attributes” text fields toward the bottom of the HTML form. Remember that you're only modeling the schema in Perl at this point, meaning that you will be free to add and remove such clauses in the future without having to send ALTER TABLE queries to the database. You also can create indices on one or more columns using the Alzabo editor.

You can use the Alzabo table and column editors to create many tables and columns, moving between them using a set of hierarchical menus and listings. The Alzabo display even places “<” and “>” marks next to each column, allowing you to move them relative to each other within a particular definition.

As you work with the browser-based schema editor, I suggest that you occasionally preview the SQL that Alzabo generates. Not only will this ensure that Alzabo is doing the right thing (as we saw with the SERIAL column), but it will give you a better sense of the low-level details your schema is creating.

After you have finished creating the schema, use the “execute SQL” button from within the “SQL preview” page to send your SQL to the database server. If the database server returns any errors, Alzabo will produce a lengthy and detailed error message describing what happened.

In some cases, you may need to fix your table or column definitions, while in others you may need to ensure that the server is running with the correct permissions. Also ensure that you have defined a PostgreSQL user (created with the command-line createuser program) whose name matches the username under which Apache runs, unless you explicitly name another user in the HTML form.