Article Preview

COLUMN

Column

Issue: 14.2 (March/April 2016)
Author: Craig Boyd
Author Bio: Craig Boyd is currently a data architect and senior consultant for a growing business intelligence consultancy. But in his 19 years of IT experience, he has been everything from a PC Technician, iSeries System Administrator, iSeries Programmer, Sr. Technical Lead, Data Modeler, Data Architect and Oracle DBA. He lives in the great state of Texas with his wife and two kids.
Article Description: No description available.
Article Length (in bytes): 7,323
Starting Page Number: 87
Article Number: 14209
Related Link(s): None

Excerpt of article text...

As a quick reminder, we are talking about database design patterns. In particular, the Party Role model. In the first article, we talked about the concept of Party and how it could be sub-typed into either Person or Organization. We then spent a bit of time talking about the concept of Person. In the second column we continued the discussion by digging into Organization and Role. Now we are going to talk about location.

I also need to make sure that everyone understands that the models we are walking through are very generic and therefore offer a great deal of flexibility, but because of that they can be complicated and technically more difficult to implement. Remember: the business and, to a lesser degree, the technical requirements should be the ultimate drivers for a logical data model. Is this the best way to model the concept of Location? Maybe, depending on the requirements. Is this the only way? Absolutely not. This is a way to model these concepts. What you should be getting out of this is the pattern of the design. Take a concept and break it down to its most logical and smallest units and associate them or relate them in a way that makes sense. As we will see below, by taking this approach, we avoid problems with postal (zip) codes.

Not every country splits up their geographic regions the same so, if you are modeling for an international application, you have to take a more generic approach. If you notice, in Figure 1, there is a GEOGRAPHIC BOUNDARY. This represents the generic location. It can be any one of the sub-types listed below it. So the approach is to insert the Geographic Boundary, type it (Country, State, Province, Territory, City, County, Region, etc...) and then associate it with its parent. Let's walk through a series of examples so you can see how this works.

First we would insert a record into GEOGRAPHIC BOUNDARY for "United States". The type code would be Country. The association would not be populated since there is no parent to a country, unless you want to include continent. A record is then inserted into the COUNTRY sub-type. Ideally, each of these sub-types would have additional attributes. For example, COUNTRY might have: population, government type, founding date, ending date, average income, median income, poverty rate, etc...

Continuing with the United States example, let's insert another record into GEOGRAPHIC BOUNDARY. This one will be "Alabama". The type would be State. The GEOGRAPHIC BOUNDARY ASSOCIATION would have "United States" as the parent and "Alabama" as the child. Additionally, a record would be entered into the STATE sub-type.

Now let's move down a level. Insert another record into GEOGRAPHIC BOUNDARY, but this one will be for the next level down from state: county. The name of this GEOGRAPHIC BOUNDARY will be "Montgomery". The type will be "County". The GEOGRAPHIC BOUNDARY will have "Alabama" as the parent and "Montgomery" as the child. And an insertion into the COUNTY sub-type would be made as well.

...End of Excerpt. Please purchase the magazine to read the full article.