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.

Advice to my 16 year old self

Oh, to be 16 once more...

I was talking with my good friend Kevin last week (my friend for 25+ years now) about what advice I would have given a 16 year old me with the benefit of hindsight. I can't say in fairness that at 16 any of this would have made sense to me but I'm going to try anyways. If this helps you then please feel free to share it. 

1. To thine own self be true

Self, it is human nature to want to please people. It's normal and expected to want to be one of the herd and to be accepted. It's normal that when your friends are publicly into punk music, for example, to want to emulate this. In fact, at times you can delude yourself into believing that you do in fact like it.

It's easy to get in the habit of following trends and following the direction of a group. But trends change, groups are formed and reorganized, and at the end of the day being honest with yourself will lead directly into being honest with others.

2. Treat people better than you'd like to be treated
Self, it's always easier to treat people negatively and to assume that people are very different than ourselves. But they are not. If you don't understand why someone is acting a certain way then almost always it's because you don't have all the information. Maybe that girl who is acting a little bit emo had a family member die recently. Maybe that jock who is bullying kids is getting beaten at home every night.

I'm not going to say do unto others as you'd have them do to you because it's too easy to be hard on yourself as well. Treat people better than you think you'd deserve.

3. Believe in (positive) karma
Self, if you are honest with yourself and others, and if you treat people as good as you can, then the universe will respond in kind. You do not bargain with the universe, however. It is not a giant adding machine where one good deed in causing another good deed out. But overall good people have good things happen to them.

My dad used to say "everything is connected to everything". The person you do a favor for today could be the person who hires you in five years. The girl who you help with your homework could introduce you to your wife someday.

4. Let your freak flag fly
Self, one of the best things you can do for yourself is find the things that you are passionate about. It can be many things from the big to the small - perhaps some obscure style of music you love, a hobby for black and white photography that you think is silly, or enjoying the fine details of fly fishing. When you find things that make you happy, pursue them with all your energy. Passion can lead you to some amazing places. And if you're honest with people about it you will find that like-minded people gravitate to you.

Your set of passions in life are part of what make you unique. No one else loves the exact set of things you do exactly like you.

5. Tomorrow is tomorrow
Self, there's nothing wrong with having a plan for where you're going with your life, but understand that every day is a new day and things can and will change. Take your career dreams right now. Were they the same when you were 5? Or 10? So what makes you think they will be the same when you are 20 or 25 or 30? You might find that girl of your dreams and start a family young. You might run into an opportunity that is too good to miss. You might find that your expectations on what working in a field is like are far different than anticipated.

Dream your dreams and make your plans, but don't be discouraged when life changes them for you. The only constant in life is change.

...less deep thoughts
There are no guarantees in life but if you look at the overwhelming odds:

• You will find a girl of your dreams
• You will start a family
• You will find a job that fulfills you
• Whatever drama you feel this moment will pass
• What seems life or death important now will not matter in the future at all
• Your sense of priorities will change. The acid test is always "did this matter so much to me five years ago? Will it matter to me so much in five years time?"
• If you're not sure whether something is right or not, trust your gut. If you're debating it then usually it's not right. Doing what's right feels right.
• Coolers are garbage, don't even bother.
• Also, drinking is not as much fun as you think.
• Smoking is a gross habit.
• If you're having sex, do it safely, else it will haunt you forever. Lots of your friends will end up with kids too early.
• You have a lifetime to experience life. Don't do stupid things that will screw that up.
• If you don't feel like you can handle life then ask for help. We all go through this.

Average vs Median

Per http://en.wikipedia.org/wiki/Skewness

Argh, sometimes people drive me nuts as evidenced with following (paraphrased) quote:

"But it is statistical fact that you cannot have more than half the population above the average and thus the statement cannot be true."

Can I say for the record this is entirely false. You can trivially prove via the following set: {1, 2, 1000, 1001, 1002, 1003}. The average of that is 668.16 which means that 4 of the 6 elements are above the average, i.e. more than half the population.

Of course, had they talked about the median I would have been OK with the statement as written.

I didn't realize that this was a common rant around my house until I mentioned it to my non-math major wife and she replied, "Of course the median and the average aren't the same. Look at a skewed normal distribution" which is a surprisingly good rebuttal.