Cookies help us display personalized product recommendations and ensure you have great shopping experience.

By using this site, you agree to the Privacy Policy and Terms of Use.
Accept
SmartData CollectiveSmartData Collective
  • Analytics
    AnalyticsShow More
    big data analytics in transporation
    Turning Data Into Decisions: How Analytics Improves Transportation Strategy
    3 Min Read
    sales and data analytics
    How Data Analytics Improves Lead Management and Sales Results
    9 Min Read
    data analytics and truck accident claims
    How Data Analytics Reduces Truck Accidents and Speeds Up Claims
    7 Min Read
    predictive analytics for interior designers
    Interior Designers Boost Profits with Predictive Analytics
    8 Min Read
    image fx (67)
    Improving LinkedIn Ad Strategies with Data Analytics
    9 Min Read
  • Big Data
  • BI
  • Exclusive
  • IT
  • Marketing
  • Software
Search
© 2008-25 SmartData Collective. All Rights Reserved.
Reading: Data Design Matters
Share
Notification
Font ResizerAa
SmartData CollectiveSmartData Collective
Font ResizerAa
Search
  • About
  • Help
  • Privacy
Follow US
© 2008-23 SmartData Collective. All Rights Reserved.
SmartData Collective > Big Data > Data Quality > Data Design Matters
Data QualityModeling

Data Design Matters

boblambert12
boblambert12
3 Min Read
SHARE

OrderModelAs 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.

OrderModelAs 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.

More Read

The Challenges and Solutions of Big Data Testing
The Challenges and Solutions of Big Data Testing
Sticking vs Backsliding – It Is So Sad
How Big Data Analytics Reveal Your Most Intimate Secrets
IDC: Decision Management Market at $10B by 2014
From Big Data to Big Personalization

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:

    • Customer service representatives addressing customer complaints have invalid records of negative events
    • Customer contact reporting and analysis is skewed

OrderModel2

  • Processes that purge old records may be unable to delete orders with mismatched foreign keys. Say you want to delete all data for Order 1. If an Order Line Event relates to both Order 1 and Order 2, the foreign key to Order 2 prevents delete of the Event unless Order 2 is also being deleted.

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.

TAGGED:data design
Share This Article
Facebook Pinterest LinkedIn
Share

Follow us on Facebook

Latest News

big data analytics in transporation
Turning Data Into Decisions: How Analytics Improves Transportation Strategy
Analytics Big Data Exclusive
AI and fund manager software
AI And The Acceleration Of Information Flows From Fund Managers To Investors
Artificial Intelligence Exclusive
sales and data analytics
How Data Analytics Improves Lead Management and Sales Results
Analytics Big Data Exclusive
ai in marketing
How AI and Smart Platforms Improve Email Marketing
Artificial Intelligence Exclusive Marketing

Stay Connected

1.2kFollowersLike
33.7kFollowersFollow
222FollowersPin

You Might also Like

data modeling
Best PracticesBig DataCRMData ManagementITPolicy and Governance

Data Design Is Not Optional

4 Min Read

SmartData Collective is one of the largest & trusted community covering technical content about Big Data, BI, Cloud, Analytics, Artificial Intelligence, IoT & more.

AI and chatbots
Chatbots and SEO: How Can Chatbots Improve Your SEO Ranking?
Artificial Intelligence Chatbots Exclusive
ai is improving the safety of cars
From Bolts to Bots: How AI Is Fortifying the Automotive Industry
Artificial Intelligence

Quick Link

  • About
  • Contact
  • Privacy
Follow US
© 2008-25 SmartData Collective. All Rights Reserved.
Go to mobile version
Welcome Back!

Sign in to your account

Username or Email Address
Password

Lost your password?