Article Preview

COLUMN

Database Design Always Matters, Continued.

You can spend a little more time upfront or a lot more time later

Issue: 13.1 (January/February 2015)
Author: Craig Boyd
Author Bio: Craig Boyd is currently a data architect for a growing consumer lending company. But in his 18 years of IT experience, he has been everything from a PC Technician, iSeries System Administrator, iSeries Programmer, Sr. Technical Lead, Data Modeler, 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): 10,868
Starting Page Number: 83
Article Number: 13110
Related Link(s): None

Excerpt of article text...

As a new year begins I have decided to do something a little different. We will pick up the continuation of the modeling problem that we have been working through in the next column. Sorry for the delay, but some things have been brewing in the back of my mind and I feel that now is the time for them to be brought forth.

I talk a great deal about data models and things related to data models and I want to make sure that we are all on the same page.

A data model is an abstract, logical definition of objects and their interactions. The objects allow us to model the structure of the data while the operators allow us to model its behavior. (Date on Database Writings 2000 - 2006, by C.J. Date, p.52)

Now, interestingly enough, look at this quote from the classic The Unified Modeling Language User Guide: "Model: A simplification of reality, created in order to better understand the system being created; a semantically closed abstraction of a system." Booch, Rumbaugh, and Jacobson are, of course, referring to UML in this context, but Date's definition is not that far removed.

Models, whether application or data, are important because they help us to understand the nature of what we are trying to implement. That implementation can be on the application side or the database side, but in reality should be done in both. I think that it is important to point this out because many times developers tend to focus more on the application side than they do the database side. And why not? They are application developers who'd rather write code than database DDL or SQL. There is no shame in that, but it only presents half the picture. Data modeling is much like application modeling which is why, to a certain degree, the relationship in ORMs (object relational models) tends to be a one-to-one relationship between classes and tables. This is neither good nor bad—it simply is where we are in the attempt to marry abstract application classes to abstract concepts that get implemented in a database. I think ORMs are very helpful in portraying the fundamental relationship between these two things, but, like many things, they are imperfect. If you are an application developer and you want to remain ignorant about all this database stuff, then you are doing yourself just as great a disservice as the data modeler who refuses to learn anything about application stuff.

My daily job is as a data architect. I work very closely with my software architectural peers to help us, together, create a solution that builds the business as a whole. I understand how things can be done on the database side to not only improve performance, but also increase the overall quality of the data and I help them understand how the data has to migrate to our back end systems in such a way that it is meaningful to our end users. For example, we currently have multiple point of sale systems (POS') in our stores (seven at last count) and each handles statuses differently. One system has only a concept of active or inactive. Other POS' understand active, inactive, work-in-progress, on-hold, etc. But our back end reporting systems have to merge all this data into a single coherent picture that gets presented to the end user. When they want total sales across a region or across the whole company, they don't want to have to run seven different reports and then try to merge them. That would be a nightmare. Instead what they want and need is a single picture that makes sense regardless of what region or store they are looking at. Software design can help with that to a certain degree, but the reality is that this is a data issue that has to be solved and only someone who is used to working through these issues can do it well. On the other hand, my software architecture peers help me to understand issues they are facing and we work together to solve them. We present a single unified picture to our developer, management, and end user communities which builds confidence both within the group and outside it. It helps us to get more done.

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