Why database design is hard

I've spent more than half my life building and working with databases in different forms. Anything from a low level data structure in C, to records in Pascal, to years of MySQL databases in LAMP stacks, through MS SQL in the Windows worlds and lately through FileMaker in the Apple/Windows worlds.

I've supported end users as they have complained about the tedium of data entry, I've helped develop queries on existing legacy systems and I've built entire frameworks from scratch.

Databases let us bring raw data to life. They let us see patterns and trending... and dig into the heart of what makes a business function.

But so most people they are a frustration... just one more thing that they have to do in their day.

Let me share a couple things with you:

1. In almost all cases, databases evolve.
It would be a wonderful thing if databases sprung into being fully formed with all supporting queries and reports ready to go for day one like Athena springing from Zeus's head. If you really want this then buy an enterprise product that's already fits into your business workflow. Nothing off the shelf that models what your business does? Big surprise. Everyone has the same problem.

The safest thing you can do as a designer is plan for logical segmented stages when you build. Start with something simple like tracking weekly production. Then ease into scheduling a small component of work. Then start integrating into other systems, etc. But take small incremental successful steps rather than trying to do everything at once. Databases evolve as time go on for good reason.

2. Databases are only as perfect as the spec and workflow that surround them.
If you want to have a productive database then plan it out on paper first. The database spec is the easiest component of this - layout of tables, relationships, etc. You can build something that looks great on paper but still suck the devils tits on a daily basis if you don't give some thought as to the workflow.

Workflow doesn't just mean how people key in each row - what are they getting out of the system? What's the perk? What's the benefit for their time? If a supervisor is keying in their production numbers then give them real time averages on how they are doing. Highlight things in green for good and red for bad. Give them immediate feedback. A database should tell a compelling story and get people excited to use it. Cosmetic things like generating maps, color coded graphs, etc. Make it interesting to those who use it daily.

3. In the rare instances that databases can't answer the question, it's because the query was unanticipated in the design phases.
I'm a firm believer that when a system fails it's almost never due to the limitations of hardware, programming languages, database constructs, etc. It's almost always that there's an unexpected angle on approaching the data - some problem that no one ever thought of.

For example, say that you build a system to track deposits that customers made. Something simple that had a tracking number, date, customers name, dollar amount. When a manager comes in at end of year and says "how many of those deposits then turned into sales" when the database was never conscious of these things then you need to be understanding of why you can't have an immediate answer.

That being said, systems can evolve per (1). So perhaps you start sharing the customer names between your two systems. And say that you build a query that then does a join between the two systems to track conversions.

Anything is doable with either perfect planning or benefit of hindsight. If you keep to incremental successful stages in database design then you'll almost always be better off.