The problem statement is very easy:
- Online store (everybody knows what is it about)
- Each user has Address.
- You can place Order for one Product.
- To simplify you have only 3 tables: Order, Product, Address.
But then when Product or Address is updated it would affect Order that was placed. Of course this behaviour would be unacceptable from business perspective. If you would like to keep this design you could make Address and Product rows immunable. So every time user changes address new row is added to Address table and every product modification is a new row Product table. This could work in this scenario but:
- There is a risk that database would grow.(not a big deal)
- How would you solve that some other tables are related to Product or Address. With every Product, Address change you should write logic to replace foreign keys with new values.
- Change of object should be update in database. That's what I feel :).
That is why, I would choose alternative approach.
It looks like denormalized and as wikipedia states:
A standard piece of database design guidance is that the designer should create a fully normalized designbut in my opinion it isn't denormalized. When order is placed you have to take a snapshot of related tables Product and Address. Since the relations Order-Product and Order-Address is 1-1 you can just add fields to Order.
What are your opinions? How can you design it better?
Maybe sth like this could help you avoid denormalization:
OdpowiedzUsuń1. Add ProductBusinessKey (uniquely identifying your product in business terms, like serial number of notebook series) to Product table
2. Replace ProductID with ProductBusinessKey for Order
3. Add ValidFrom and ValidTo date fields to Product table (for current product description ValidTo field is unset)
4. Add OrderDate field to Order table
Then you can keep track of product changes and always find the one description that was valid at the time the order was made.
The same goes for address:
1. Add Person table with PersonId (I personally think that omitting Person here is a bit of oversimplification)
2. Replace AddressId with PersonId
3. Add PersonID foreign key to Address table
4. Add ValidFrom and ValidTo date fields to Address table (for current address ValidTo field is unset)
Hi Andrzej,
OdpowiedzUsuńThe case you've described is a classic example of slowly changing dimension (SCD) for product :)
You can find a lot of documents which may be helpful for you (even Wiki page is quite good ---> http://en.wikipedia.org/wiki/Slowly_changing_dimension).
I highly recommend first part of Paweł's solution (product table). Second part is also OK, in case you want to keep history of user's addresses. But if you don't want to keep history, your first solution is the best in my opinion (AddressId in Order as foreign key to Address table). Why? As far as I understand what is the content of Address table, every row describes some address (for example Marszałkowska 1). And if user is changing his address (let's say to Marszałkowska 666), you should only change AddressId in some Users table, and do nothing in Order. Then all old orders are connected with correct address (real delivery address).
Paweł and Ewa, thank you so much. I've seen this problem many times but nobody really knew how you can cope with it and what are consequences of your choice. You're the best!
OdpowiedzUsuń