Jonathan Stark

One of my quick question folks asked me a great question the other day:

I’m working on the structure of a contact management system for my university. It’s a rewrite of a successful system that’s been running for almost 10 years. I’m having thoughts about how People relate to Roles and Organisations and how Communications or Address tables relate to these etc. For example one person can have many roles (student, employee, agent etc etc) in many organisations. They can have many phone numbers addresses etc etc according to their Role and Organisation.

I’m trying to build a better structure this time to take into account the fact that lots more people will start using it. I also don’t want to repeat the sins of the past I know the above is a relatively a common dilemma. I’m pretty sure I can work it out well, however I’ve thought about it too much and have become immobilised!!

Keep this in mind:

You’re never going to get the data model perfect.

There is no such thing as the perfect data model. You just need to get it pretty good. If you over normalize, programming will be a nightmare. If you under normalize, programming will be a nightmare. Even if you did nail it up front, the business needs will eventually change and invalidate it.

The good news is that there is a wide area in the middle that will be fine. When you are familiar with the solution, whatever you do will probably be fine. People usually get into trouble when they are new to the data and aren’t really aware of the idiosyncrasies lurking beneath the surface.

I wrote an article a few years ago that took data abstraction to the extreme. I used this approach for a number of projects and I have to concede that in most cases, it ended up being a giant pain in the ass. My advice is to trust your instincts and go with the most obvious solution. If you are having a hard time deciding between two options, it probably doesn’t matter which you choose.

Hopefully, others will share their experiences in the comments.