We tend to have an aversion to things that have given us a negative experience in the past. I hate limits! Every fiber in my being begins screaming when I’m faced with baking a limit into a system. Every time I’ve ever designed a system with limits I’ve ended up having to rebuild the system to remove those very same limits later on. Every time!
There’s tremendous emphasis on code design but there’s precious little the code can do for you if the database schema is full of limits and is thus unable to house the data. Take for example a simple client database schema. A typical schema I’ve seen time and time again is;
- Address 1
- Address 2
- Postal Code
- Home Phone
- Mobile Phone
- Work Phone
The first limit we see if that this person can only have one address. No work address and no address history! If we’re selling house insurance and the customer moves, we could find ourselves automatically applying the old policy to the new house which may have a much higher value because we’ve thrown away all reference to the previous house. We’ve also tied all of the phones to the same address and while changing the address will automatically associate all old phone numbers to the new address, the home phone number is likely to be new which invites associating a dead phone number to an active client.
The second limit which is a little worse from a design perspective is the phone numbers. Currently I have 3 phone numbers but at one point I had as many as 6 personal phone numbers.
Data designs like this often end up with bandages in the schema to accommodate the things that were overlooked without having to rebuild the system but those bandages tend to cause far more trouble than the rebuild would have. The answer is to avoid limits.
The Client Database Schema was a database I designed for a local phone card supplier. The system not only avoid limits, it does so to a ridiculous level which is to say the likelihood having needing redesign or bandages due to database limits is greatly reduced.
- A single person can have an unlimited number of addresses. You can track ALL of their old addresses as well as ALL of their new ones.
- A single person can have an unlimited number of phone numbers. Again, you can track old numbers as well as all new ones and the number of current phone numbers can grow or shrink with the client’s situation.
- A single person can have an unlimited number of email addresses and once again you can track old and current.
- A single person can have an unlimited number of companies they work for or own. This facility only tracks the current companies but you can see how it could be expanded to track company history as well.
- And a single company can have an unlimited number of branch offices, unlimited number of email addresses and an unlimited number of phone numbers.
Although the schema does allow the association of an unlimited number of phone numbers and email addresses to a single corporate entity, it ties that information to a corporation and not the location, so it’s not ideal for tracking location uses which phone numbers or email addresses but the schema could be modified to permit each company address to have its own unlimited collection of phone numbers, addresses and email addresses. In my case, this lack of association was so far out of the clients reach that I opted to take that limit in favor of reduced complexity.
Here’s the Schema done in MySQL;