When it comes down to it, a web application is only as good as the architecture behind it and a large part of that will be the database design and schema. Good data structures can mean the difference between your system being scaleable and robust or your system being covered in a whole heap of metaphorical band aids within a few years.
At Revium, we put a lot of emphasis on the importance of planning out a project and putting in place a specification for our developers to work from. The reason we do this is because complex systems can end up having a lot of dependencies between their different parts and without a considered approach this will lead to numerous problems.
One of these problems which I will talk about now is the build up of data over time and how this can be affected by your choices in database structure. At Revium, we have developed an eCommerce product that allows us to build scaleable, configurable sites that can sell online, one of the problems we faced with designing this platform is that time changes things.
Think of a product, think of its price. Does this price remain constant over the product lifespan? The answer is almost always no, prices go down, prices go up. Some prices will remain the same for a long time, but you cannot guarantee they will never change. So what does this have to do with database design?
If you wanted to calculate the total revenue for sales of a product within an eCommerce system, it is simple…
current price of product x quantity sold
Easy… or not, as the products price might have changed over time and we need to be able to account for this. So we need to allow for the storing of the price that a product was sold for at the exact time of sale. In most cases, to represent an order consisting of multiple items within a database, you need a minimum of two tables, an order table to store the details of an order and an order items table to store the items that the order consists of. So to store the price at time of purchase you would need to add a field for item price on the order item table. Now you can look through the order items table and do the following…
sum ( price of product when sold x quantity sold)
And now you will have an accurate reflection of how much revenue a product has generated over time.
Obviously, this post is just food for thought and every database is different and has a unique set of constraints. It is however very important that you think about how your database can grow old gracefully when you design it, your web applications will only be better for it.