Recently I was involved in a project for a system that has ever expanding requirements. That’s really cool. It shows you that the product you created has proven so useful to its users that they want to extend it further to handle other aspects of their business.
In this project there are a few features that are similar but different. The day is organized using the same relational structure. The difference is that when you get down to the lowest level of data items, there can be variance in the data structure. Think of a customer who has a shopping cart. But, the things you put into the shopping cart have different attributes. So, you may have books, computers, food, etc. each with their own unique set of attributes, even though each is only a single item.
From history I have found that I would probably have a data type such as a shopping cart item. Then, I would relate that specific item to different tables depending on what was needed. I could relate it to a book, food, electronics, etc. table, all at the same time. But each shopping cart item would only be related to one of the detail tables. So, there is a one to zero or one relationship of the shopping cart item to the item specific detail table.
This is a great data structure. What it means is that each time the business comes up with a new kind of item they want to manage, then a new table has to be created to store the item specific data. This isn’t always bad, because you usually have to open the application code to be able to accommodate and manage the different data types in your application as well. So why the shopping cart item table? Because having that single table allows you to build all of the generic code needed to add an item to the shopping cart, sum up the cost, extend the number of units desired times the cost each, calculate sales tax, etc. All of those features are common to everything that can be a shopping cart item.
There have been very few applications I have worked on that didn’t come up against this problem at some time or other. Usually you can push back and get the business to fully complete their data requirements. Sometimes, the data requirements are always changing, and the application has to be able to evolve along with the business needs.
This is one solution to a common problem where there is a base requirement that is unchanging. Tomorrow I’ll share a few other implementations we considered that could have solved the same problem.
Cheers,
Ben