A friend runs a custom made webshop. The webshop is built by a webdeveloping company and is running since a year. There are still many bugs. Especially with numeric values and EAN codes (13 character numeric codes). There is no budget for switching over to an other firm and the current firm says the system is ready.
I've studied database design about twenty years ago (but never used it since) and was asked to review the webshop.
I've reconstructed the database schema from hardcopy and was shocked by the design. The same (type of) data is stored in multiple tables and is differently declared in different tables. I now need a sounding board so I can motivate within a report what I think about the database design. Can you please review the design and comment.
Database is not normalised and (thus) contains a lot of redundance data.
E.g. address is in ws_extra_sites, ws_suppliers, ws_customers and ws_orders.
There is calculated data (price_inc, price_tax) in ws_orders_products and ws_products.
Thanks for your thoughts.
I can imagine that there is a full address in ws_orders. After delivering that address will not change and is historical data.
But a supplier can also be a customer in which case the personal data of the person and his or hers address is redundant. In this case there are a lot of suppliers also customer.
Supplier Address and customer address are different fields. IMHO, you can over normalize and make a mess of things. If you are always pulling the address when you read a customer/supplier, that results in two row fetches not one. I have no problem having duplicate data in the system for different entities. If the supplier moves his receiving to a different address, it's a lot simpler than trying to split up address records.
Normalization's goal is to make the database efficient, not to remove any chance of duplicate data.
I have a web based system and watch the execution times closely. I have taken dog reports that ran in minutes, optimized the SQL code, and the report dropped to under a second. remember INDEXES ARE YOUR FRIEND
I saw an over normalized database with one address table for various things, ran like a three legged dog.
So many people so many ways to get to Rome.
My friend finds it important to to be precise. So there has to be a mechanism which triggers the fact that the customer is also a supplier and vice versa.
i.e. A product can be sold by several suppliers. Then it comes in handy to know when a supplier is customer with great buying potential.
Changing a suppliers adress and not changing a 'duplicate' customers address is not neat.
The fact there is a discussion over this subject makes it important.