In my previous article I introduced the HoudiniESQ (ESQ) default database (Derby) and a great database tool (dbVisualizer) for managing access to the database from outside of HoudiniESQ. We are now equipped to discover a few basic fundamentals of the ESQ database architecture.

The ESQ database design is truly “relational” and prevents data logic corruption commonly found in database designs that lack normalization. To illustrate this concept consider changing a Contact Name, the database does not need to go through the gyrations to update everywhere the name was used. The Contact is linked referentially and is not copied into the various records where the Contact was used, like in an Event.

ESQ’s consistent column naming makes it easy to identify relational links, dates and other commonly used data elements. For instance, the unique identifier in all ESQ tables will always be called GUID, an industry standard term for “globally unique identifier”. This GUID will be used in through the ESQ database schema to create relationships to other tables. In the naming conventions. For instance, every thing before the word GUID will be the name of the related table (that holds the related data). An example would be RECORD_TYPE_GUID is the GUID pointing to the RECORD_TYPE table in the ESQ schema. Let’s take a look at how this works with the Contact table.

GUID usage in the ESQ Contact Table:

    CONTACT.GUID, is the unique identifier for the underlying Contact record.
    CONTACT.RECORD_TYPE_GUID, is the identifier for the underlying Contact’s record Type.
    CONTACT.STAFF_GUID, is the identifier for the Contact record’s assigned Staff.
    CONTACT. CLASSIF_CODE_GUID, is the identifier for the Contact’s Classification.
    CONTACT.STATUS_CODE_GUID, is the identifier for the Contact’s current Status.

Let’s take a look at how we can use this in the real world. As you may know, ESQ takes full advantage of BIRT, an open source report writer similar to Crystal reports. BIRT is best in class and I highly recommend it in lieu of Microsoft SQL Reporting (I’ll tell you why in my next article).

The following select statement results in a user friendly Contact result set where the GUIDs have been replaced by a meaningful description (instead of a cryptic identifier).

SELECT t.name        AS type,
        s.staff_name AS staff,
        cc.name      AS class,
        sc.name      AS status,
        c.*
        FROM contact c
        LEFT OUTER JOIN record_type  t  on c.RECORD_TYPE_GUID = t.guid
        LEFT OUTER JOIN staff        s  on c.STAFF_GUID       = s.guid
        LEFT OUTER JOIN classif_code cc on c.CLASSIF_CODE_GUID= cc.guid
        LEFT OUTER JOIN status_code  sc on c.STATUS_CODE_GUID = sc.guid

Since BIRT has the ability to create something known as a Data Set, this query can be saved with a meaningful name and used for reporting purposes instead of directly using the Contact table.  It should be noted there is additional overhead caused by the JOINs so use joins judiciously, when you need the information provided by the descriptive elements in the related tables.
 
 

• < Prev
• Next >