Bob Lambert is a CapTech Director and Data Architect interested in information management, business analysis, databases, & projects, and how IT and business get together to plan, build, and maintain business value. You can comment on most of Bob's posts at http://robertlambert.net.
Data Design Matters
Jan 08 2013
As important as it is, data modeling has always had a geeky, faintly impractical tinge to some.
I've seen application development projects proceed with a suboptimal, "good enough", model. The resulting systems might otherwise be well-architected, but sometimes strange vulnerabilities emerge that track directly to data design flaws
Recently I saw an example where a "good enough" data design, similar to the one pictured, enabled a significant application bug. My fictionalized model illustrates this case's anti-pattern. Say there's an order management system tracking Orders, Order Lines, and "Order Line Events", customer transactions involving an order item. The Order Line table includes a foreign key to the Order table. The Order Line Event table includes foreign keys to both the Order Line and the Order.
The latter relationship from Order Line Event to Order is logically unnecessary. Each Order line is related to exactly one Order, so if an Order Line Event relates to an Order Line it must also relate to a specific order.
Beyond being unnecessary, in this case the extra relationship turned out to be harmful. Somehow the online system had a bug that updated an Event's relationship to a different Order than the related Order Lines. So a single Order Line Event could be related to two separate Orders, one through the Order Line and the other directly through the Changes/Changed By relationship.
In the real example there were some specific impacts that I won't go into, but you can imagine the possibilities. Here are just three:
So, a database design that seems imperfect but "good enough" in fact isn't. In this case it would have been well worth taking extra time during design to prevent the chance of subtle but significant errors in the application.