• The cover of the 'Perl Hacks' book
  • The cover of the 'Beginning Perl' book
  • An image of Curtis Poe, holding some electronic equipment in front of his face.

Death By Database

minute read

Find me on ... Tags

The following is a true story, but with names changed.

When I work with clients to build software, I take the usual steps of understanding their needs, gathering requirements, learning about their customers, and so on. At this point I have a model on paper of roughly what the software is intended to do, so they get surprised when I immediately turn to database design.

"Who care about database design? What about mockups? What about workflows?"

Let me tell you about "Bob's Luxury Goods." I worked for this company many years ago and they had a retail store selling ... you guessed it ... luxury goods. They'd ask all customers for a billing address and if they had a different delivery address. At the database level, they had a "one-to-many" relationship between customers and addresses.

That was their first problem. A customer's partner might come into Bob's and order something and if the address was entered correctly it would be flagged as "in use" and we had to use a different address or deliberately enter a typo. Fortunately, addresses were case-sensitive, so many people had UPPER-CASE ADDRESSES.

We should have had a many-to-many relationship between customers and addresses so we could handle the case where more than one person would share the same address, but we didn't. Further, I was never allocated the time to fix the database because it was "cheaper" to remove the restriction on "flagged" addresses and allow a duplicate address to be used.

Naturally, being a luxury goods company, we had many repeat customers and sometimes they would move and if we didn't find the duplicate address, or the address with the "typo", we might update the address for one partner, but not the other. That was a headache, but it didn't happen frequently enough for management to worry about it.

That's when the marketing department had a brilliant, inexpensive idea. You see, we periodically did mass mailings of special events to our customers. Since we had the software to do mass mailings, why not import a mailing list of all addresses in high net worth areas and mail everyone about upcoming special events? So the company went ahead and bought a database with all of these addresses, but forgot to mention to me that I was supposed to implement this.

Except that every address record had the customer id embedded in it, so we couldn't enter an address without a customer.

"Curtis," they said, "just enter a dummy customer called 'Occupant' and attach all addresses to that."

Except you couldn't enter a customer without an order.

Except you couldn't enter an order without at least one item on it.

Except you couldn't enter an item unless it was listed in inventory.

Except that reserved the "inventory" item and made it unavailable.

Except, except, except ...

It came down to trying to create a fake customer, with a fake order, with a fake item, with a fake item category, with a "paid" invoice, with exceptions sprinkled throughout the codebase to handle all of these special cases and probably more that I no longer remember.

Then, and only then, could I write the code to provide "generic" mass mailings. Management decided it was easier to hire an outside company to handle the mailing list for them.

If they had simply had a proper database design up front, they could have reused their existing system with little trouble.

That's what bad database design costs you and why I usually start with that before writing my software.

Note: if you're not familiar with database design, here's a talk I give where I make it fairly simple to understand. I mostly avoid big words.

Please leave a comment below!

If you'd like top-notch consulting or training, email me and let's discuss how I can help you. Read my hire me page to learn more about my background.

Copyright © 2018-2024 by Curtis “Ovid” Poe.