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.

Mobility

I was flipping back through the photos of my son over his (almost) year of life and looking at, in particular, the mobility related shots...

Apr 12 2011 - The demise of the baby swing

Jun 9 2011 - Rolling

Jul 17 2011 - Much more practice with rolling

Aug 13 2011 - Full on crawling

Sep 28 2011 - Sitting up

Nov 12 2011 - Very hesitant wall surfing

Nov 18 2011 - Furniture surfing

Nov 20 2011 - Using a walker

We're not walking on our own yet, but we're damn close. :)

Build a better lock (or locksmith)

My job often comes with unique challenges and unexpected learning opportunities. Recently we were approached by one of our vendors and asked whether we'd like to know how to key/rekey locks.

I have to say that having rekeyed a half dozen locks now I have a lot less respect for the security of my front door.

If you're interested in how a pin tumber lock works then read wikipedia as it explains it well.

It doesn't look that complicated (it isn't that complicated!) but the first time you see a bump key in action you realize how easy it is to get into a lock. Putting that a different way, I buggered up the middle pin of a lock that I was rekeying at work the other day. In about three seconds with a bump key the lock was opened and I was able to fix it.

Granted, there are more sophisticated bump resistant locks on the market (i.e. different tensions of springs on the individual cylinders) and really you should have home monitoring or an alarm system anyways but any illusion that a lock would hold a motivated person out is nonsense.

Politico

One of these days I need to outline my list of ridiculous political beliefs. I generally identify myself as a libertarian but I'm kind of a mutant offshoot. For example:

• I'm socially left and fiscally right
• I don't believe in any political party pandering to unions as a whole
• All marriages are equal whether same gender or opposite - polygamy is still out :p
• Sex with anyone under 13 illegal no matter what age you are - sex with someone 13 and up legal as long as both within three years and neither in a position of authority
• You can be a church or a corporation but not both. You can own the land your church and facilities is on, but nothing else.
• Legalize government brothels - mandatory screening period on prostitutes including education/assessment (similar on johns), mandatory STD testing on both, you have to have tubes tied to be prostitute or similarly rendered infertile if male. Anyone prostituting outside of this gets 1000 year sentence.
• Legalize government drugs - mandatory screen period on users including education/assessment. Anyone buying/selling outside of this gets 1000 year sentence.
• Criminal code - first criminal offense nets you government training/education, second criminal offense gets you shipped to NWT to work in camp for 5-10 years, third gets you 1000 year sentence.
• People aspiring to political office give up all assets when elected, get returned assets plus/minus bonus based on measured performance in office. That is, if economy tanks, you pay like the rest of us - if you succeed in improving country's lot then your bonus matches this.
• Dissolve any organizations ability to presume guilt - i.e. human rights tribunal, WCB, CRA
• Firearms should be freely available to anyone who passes the appropriate tests. I'm strongly opposed to gun registries.

Those are my quick thoughts but you get the idea. I don't mesh up to any Canadian party by a long shot ;)

What you could sell me...

OK, heads up to all you retail jockies out there: I am never going to buy a $50 warranty plan on a $50 piece of retail electronics. It is stupid to ask and it makes me angsty.

I would, however, give serious thought if you pitched the following:

"Tell you what... for $50 you get priority warranty service. This means that you get direct phone access to a tier 2 support person who doesn't make you walk through a stupid script and has the authority to drop ship you a replacement unit with a prepaid return box for the NFG one. For your $50 we'll honor this up until the end of your three years and if it turns out that your model is replaced then we'll give you a free upgrade, no charge."

Don't get me wrong - I didn't buy the $50 router in question... I understand the difference between low end residential gear and business equipment, and I understand the need for tier 1 to weed out all of the stupid people.

But when I call up and say, "hey, I'm plugged directly into this router. No other devices, no internet connection to the unit. I can't ping it on the default IP. I can plug my computer using the same cable into another unit and have it work. If I hold reset I can reach a firmware flash screen for the router but otherwise it's non-responsive. I've checked out your site, there is no firmware file of any kind or I would have already flashed it." and the response is "Sir, I must know the name of your internet provider and the manufacturer of your modem" it makes me die a little inside. They seriously explained to me three different times how to push a reset button.

Seriously retail vendors out there - I would double the price of any unit on the shelf anywhere if it meant I could talk to an intelligent person and be treated like a normal business support call.

I love ICE

One of my favorite tools in my big bag of photography tricks is Microsoft ICE. Essentially ICE lets you create composite panoramics.

You can take any number of photos and then feed it through ICE and it magically combines them together for you.

I have, for the record, used a number of other tools - i.e. Adobe Bridge -> Photoshop is one way, there are some homebrew filters for GIMP and Hugin is another path. In short, though, ICE is by far the best tool.

A couple caveats:

• If you're shooting with an SLR camera, check if you have an exposure lock option. Autofocus on your first point, set your zoom to manual so it doesn't change, lock your exposure at your median shot and then capture as many overlapping shots as you can.
• If you're shooting with a normal shutter speed, you generally do not need a tripod. That being said, if you're doing late night photography or sunset/sunrise shots then you might want to use a tripod. Tripods are nicer for being able to articulate a set number of degrees per shot but generally speaking ICE doesn't care if you overlap exactly at 5 degrees or if you capture a bunch of shots willy nilly.
• If you don't have exposure lock then use one of your manual modes, ideally a straight M mode, and set your camera for the median shot. Then capture per normal.

If you don't have an exposure lock and can't work around it, generally ICE can compensate anyways. However I find that I get a much better quality picture in general if I can exposure/focus lock.

Here's a concrete example of a shot I did near Kamloops. Something like 20+ 10MP photos which stiched into a ~48 MP shot:

Easter, sushi and print sales

Another weekend and another 800 km driven through Northern BC. We had an epic time with Dick, Eva and Kenny in Dawson Creek.

Patrick traveled relatively well - it took us an extra 1-1.5 hours to get there but we arrived calm and collected.

On Easter Sunday, Carol and I were driving around looking for a good place to walk when we decided to start a new tradition - Easter Sushi! I had probably the best spicy tuna roll I've ever had, bar none, at a lovely little sushi bar in Dawson Creek.

We took some amazing photographs of the Easter sunset - Carol managed to get some telephoto shots of a white tailed deer - and we made it back to PG without issue.

I was quite pleasantly surprised to find that I've sold a pair of prints while I was away - both local PG shots and both oldies. That's always a pleasant way to start the week.

In short, good times, great family visit, wonderful Easter!

P.S. For those who likely creepy Easter bunny photos:

Weird dog commands

I forgot, somewhat, over the last couple months how much I miss interacting with my dog. During the first couple months we were at home, my roommate had taken over the dog. That is, the dog was living in a different part of the house. Alternately put, he was having sleepovers downstairs.

This came about for what I thought were reasonable reasons at the time. Patrick was not sleeping through the night and I thought the dog reacting to new baby would add unnecessary stress. To be honest, having him away was a relief on some levels.

Having him back, though, has been fantastic. I forgot how much I interact with the puppy on a regular basis -the constant reinforcement of behavior and modifications thereof.

The newest command that we're working on (don't ask me why I didn't do this years ago) is "go poop/pee". As in, snap of the fingers in a direction along with command. In past years I would let the dog outside to do his own thing and bring him in after 5-10 minutes. Or I would hang out with him in the back until he went to the washroom. This is less practical with the cold snaps that we've been having (it was -20C outside this morning) so I had to figure something out.

How do you train your dog to go to the washroom on command?

1. Start with success. That is, wait until he really has to go before you start trying to train the behavior.
2. Reinforce positive behavior with positive actions. Erik is very praise oriented and because he is not free-fed any food treats (i.e. piece of kibble, chunk of discarded green pepper) make him very happy.
3. Continually reinforce the same standard. Given that I have obsessive compulsive behavior this is fairly trivial for me.
4. Add to the behavior once the dog is consistently performing. In Erik's case after a week or so I realized that I didn't like him pooping so close to my carport so started the snapping and pointing to indicate direction. It took him a day or so to grasp what I was indicating but now I can quickly turn him around whenever he gets within 10' of me.

This is not for everyone but changing bathroom breaks from 5-10 minutes to a minute even alone made it worthwhile for me. The added benefit of directing his waste somewhat is also satisfying in its own way.