Recently I’ve been doing allot of database planning for an eCommerce related product. In the process of doing so, I realized one problem allot of beginner and intermediate programmers have is properly designing a database schema. It seem’s the database structure almost always matches the user interface structure. This sounds logical, however can actually limit what you can do quite a bit.
If you have a product for sale, and that product has images, prices, categories, weight, etc…. In the user-interface, when someone is setting up a new product, the price and weight is filled out by the user while creating the product. This makes perfect sense for a user interface, however – not for a database schema. In the database, the weight and price should be based off of combinations of options for that product – and not tied directly to the product.
Say you have a Playstation3 for sale. The price shouldn’t be tied to the product “Playstation3″. Instead, the price should be tied to an inventory record for this product. Such as Playstation3 – 60GB. The size of the hard drive is an option for this product, which effects the price – thus, the price on the product level is now useless. Your database should reflect this, however the user-interface needs to be designed so that this logic is hidden from the user. (I’ll leave that task to a designer, don’t ask me how).