SID and Database Design

Was back in the US again this past week teaching near Denver.  Celebrated Cinco de Mayo at a great little Mexican restaurant in Lakewood, Colorado, where I enjoyed Puerco Adobado, marinated pork in spices.  Here’re the links to the restaurant and a recipe in case you want to try a true taste of Mexico...

http://tapatiomexican.com/index.html

http://www.astray.com/recipes/?show=Chuletas%20de%20puerco%20adobados

The recipe calls for pounding the port flat, which is something to consider when implementing the SID from a database perspective.  As a technology-neutral information model, the “I” component of the SID has never been intended to be implemented as-is.   Parts of the SID include multi-level class hierarchies that are not necessarily suitable for one-to-one SID entity to database table implementation.  There are three options (at least) that are open to database designers.  From a visual perspective, you can think of collapsing a hierarchy:

1.       From the top down – collapsing all super-classes into the lowest level classes

2.       From the bottom up – collapsing all sub-classes into the highest level super-class

3.       From the top and bottom to some intermediate class – hopefully the visual is apparent.

There are considerations that should be taken into account for each option, some of which are included here.  There are certainly others.  Option one results in duplication of attributes; therefore, if the properties of the attribute change, care must be taken to ensure they are change across all the tables.  Option 2 results in many attributes and associations that were “required” in a subclass to become “optional” in the super-class.  The conditions under which the attributes and associations are still required must be carefully documented.  Considerations associated with the third option include those of both option 1 and 2.

The SID is a non-redundant decomposition of information, meaning that many entities, such as Individual Name and Location, are only modeled once.  This ensures consistency of how these entities are represented in the model.  This does not mean they have to be implemented this way.  For example, it is not required to implement a single set of database tables that contains address information.  It is certainly acceptable to include address information as part of a Customer database or a Resource Inventory database.

As a final, but certainly not the last consideration is how to implement the “Characteristics” included in the SID.  First, the specification part of the model should be cached in some way to ensure acceptable performance.  And, although Characteristics are duplicated across a number of domains (this practice will no longer be continued in the SID), a single set of shareable tables can certainly be implemented.  The Characteristic model that is contained in the Root Business Entity ABE in the Common Business Entity domain can be used as a “template” for this.  If there is a concern about performance, an implementation could include a set of tables for each domain, shareable by entities where Characteristics are used by entities within a domain.

I’ll be off to Management World Nice next week, hopefully, I’ll have time to blog from there!  The topic most likely will be a follow up on this one that talks about how implementation changes introduced in database design and API design can conform to the SID