Dr. Danielle Ofri has a frightening post about how data design can cause serious consequences. She talks about running up against a 1000-character limit when trying to create a patient history for someone about to undergo a risky surgery:
I panic for a moment, fearful that the computer has frozen and that I’ve lost all my work — something that happens all too frequently. But I soon realize that this is not the case. Instead, I’ve come up against a word limit.
It turns out that in our electronic medical record system there is a 1,000-character maximum in the “assessment” field. While I’ve been typing, the character number has been counting backward from 1,000, and now I’ve hit zero. The computer will not permit me to say anything more about my patient.
If you’d done any database design, you know that even if you design a good, business-driven design, others who use the database might apply their own rules to the data on the way in or out of the database.
I remember designing a Point of Sale system database for an appliance retailer. Our data model needed to support the selling of high-end appliances as well as bulk purchases for high-end appliances. So our transaction amount columns were significantly large. A developer on the application team thought our monetary field lengths were insanely large, so he enforced a limit on a transaction of $9,999.99 for the total transaction. To make matters worse, this system went all the way into production with that limit. So on day one of the roll out, sales people couldn’t sell the most highest margin items such as a professional quality stove ($14,000) or sell to developers who were buying 100 low end stoves in one transaction. Their orders had to be chunked up into tiny $9,000 mini-transactions. Order completion was taking hours instead of minutes. Credit cards were being rejected for too many large amount transactions back to back. In other words, a nightmare deployment because organizations trying to spend tens of thousands of dollars were walking out and going to other retailers with “real systems” to make their purchase.
However, no lives were being lost (that I know of). Some people may have gone longer without heat (furnaces) or with rotten food (freezers and fridges), but in the overall scheme of things the impact on customers was not life and death consequences.
If we get back to Dr. Ofri’s situation, though, she was faced with a terrible data dilemma: how to describe a complex patient history in 1000 characters. This number probably sounded like a huge number when the project team was adding that “feature” to the system. I’d even bet their own test data only went as high as 200 characters or so.
I’m also guessing that since this system is a fairly high-risk project that some expert user (or many) was responsible for approving the design of field lengths. Perhaps he or she also thought that 1000 characters was enough.
In desperation, I call the help desk and voice my concerns. “Well, we can’t have the doctors rambling on forever,” the tech replies.
That response from IT (even if it a help desk tech who has no clue as to why there is a limit) makes me mad and afraid at the same time. You’ve all heard it in your design reviews, haven’t you?
- That’s way too long of a field.
- It won’t fit on one window without scrolling
- No one has an e-mail address THAT long
- You are over modeling it
- That’s ridiculous. No one is going to sit there and type that long
- 255 was good enough for the last 10 years, it’s good enough for the next 10 years
- The indexes on that column will be too large
- Go ahead and make the column that long; we’ll just truncate it in the application
The business users are frightened by the negative comments and agree that 25 is sufficient for e-mail address, not even realizing that some of their own e-mail addresses are longer than that.
As I blogged recently about Over Modeling, it’s only over modeling if it doesn’t meet the business need. Sure, we might make concessions to technical feasibility (“make every column 2000 characters, just in case”), but our designs should be business driven.
Dr. Ofri ends her story by saying:
I’ve finally condensed my patient’s complicated medical conditions to exactly 1,000 characters. I quickly hit “save” before I lose everything. I wish him good luck on his operation, wondering if his surgeons will have to condense the entire operative report to 1,000 characters as well. What happens if there are complications?
For my next medical evaluation, I think I will use haiku.
I don’t know about you, but I wouldn’t want to read that about my own patient record.
Next up: What could we have done differently on our project
Leave a comment
Subscribe via E-mail
- My links of the week – December 8, 2013 | R4 on 10 Tips for the Minimalist DBA
- Doing It Right: Performance Monitoring and Troubleshooting - SQL Server - SQL Server - Toad World on 10 Tips for the Minimalist DBA
- Doing It Right: Performance Monitoring and Troubleshooting - SQLRockstar - Thomas LaRock on 10 Tips for the Minimalist DBA
- Robert L Davis on Trolling the #24HoP
- Cindy Gross on Strutting: We all Know When You are Doing It. So Stop.
- December 2013 (1)
- November 2013 (1)
- October 2013 (3)
- September 2013 (3)
- August 2013 (2)
- July 2013 (4)
- June 2013 (5)
- May 2013 (7)
- April 2013 (5)
- March 2013 (4)
- February 2013 (7)
- January 2013 (12)
- December 2012 (2)
- November 2012 (3)
- October 2012 (3)
- September 2012 (13)
- August 2012 (5)
- July 2012 (17)
- June 2012 (2)
- May 2012 (4)
- April 2012 (4)
- March 2012 (8)
- February 2012 (11)
- January 2012 (3)
- December 2011 (10)
- November 2011 (8)
- October 2011 (5)
- September 2011 (3)
- August 2011 (9)
- July 2011 (5)
- June 2011 (5)
- May 2011 (5)
- April 2011 (9)
- March 2011 (4)
- February 2011 (9)
- January 2011 (8)
- December 2010 (15)
- November 2010 (27)
- September 2010 (2)
- August 2010 (1)
- July 2010 (4)