Is Your Database Schema Limiting Your Productivity?
The term database is frightening to many people because it seems all geeky and technical. If you have any organized information pertaining to your business, (even something simple like a leads rolodex,) then you have a database. You could continue to use your physical rolodex; you could put all your data into a spreadsheet; or you could buy or create a solution that is backed up by a modern relational database management system (RDBMS). In the last decade, sophisticated database technology has become much more available to the ordinary person.
A huge number of people do not use the potential of RDBMS technology. They merely turn their Excel spreadsheet into an Access database and use it the same way as before. The key to remember about a RELATIONAL database is that it is able to map relationships between one set of data and another. It takes a few tries before most people get their head around this. Even people who know better often create “dumbed down” data models for their clients because it is easier and cheaper than trying to explain relations, and the clients will be back either for complicated expensive programmatic solutions or for an expensive data overhaul later when they have a lot of data in their system. Much of this headache could be avoided if they had a good data model to begin with. For example, one charitable organization wanted to track their donors’ history. So in their spreadsheet, every year they added a new field for that year. Their spreadsheet was getting bigger and bigger, and there was no way to archive old data because they kept adding onto it.
We did a schema redesign and we created a very simple relational model for them. We separated the donations from the donor, and we related them through donor number. We also noted the year of the donation as a data point rather than a field heading. So now, instead of one huge spreadsheet with everything about a given donor, we have two — one for donors and one for donations. RDBMS systems have a lot of power built in. If you take advantage of the powers of the RDBMS you will have less application code to maintain. No where is this more obvious than with the SELECT queries. If we want to see all the donations for 2006, we just constrain our SELECT to donations where the year is 2006. If we want the donation history for donor X, we constrain our search to donations where the donor id matches X’s and exactly the datasets we want are there without doing any application code at all to get it.
If your data seems unwieldy and awkward and deep in your mind you sense there has to be a better way, there probably is. Let a database expert look at your schema. Once you start using it appreciating it you will slap yourself on the head and say “Duh, why didn’t I think of this?”
Author: Colleen Dick
Colleen Dick has additional technical articles and humor on her Hot Dorkage blog; also some more in-depth technical articles on Hub Pages She is not selling anything.
Comments
Feel free to leave a comment...
and oh, if you want a pic to show with your comment, go get a gravatar!
