Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mathmax
Yak Posting Veteran
95 Posts |
 Posted - 2009-11-11 : 21:28:29
|
| Hello,I would like to design my database to contains the following informations:- a list of deliveries- each delivery contains a list of products that should by grouped by supplier.So we should have three tables:delivery 1<->n supplier 1<->n product_quantityWhere things get complicated, is that I should also represent a planned delivery (before the goods are actually delivered). And there could be several versions of planned delivery. Beetween two versions, there could be some supplier addition/deletion and some product_quantity addition/deletion.There could be 1 to n planned deliveries for only one effective delivery. The effective delivery handles informations that are filled at reception.But the real problem is when you create a new version of a planned delivery. It would be very heavy to create a new list of suppliers with all there related products, only because you have to change some quantities or remove/add some products for your planned delivery...I could keep the id of latest scheduled delivery when creating a new version and only add the modifications compared the the latest version, but It could be also quite heavy when there is a lot of modifications...An other possibility would be to have n-m relations like this:delivery_planned n<->m supplier n<->m product_quantityHow do we use to deal with versioning ? For example, how would you keep several order versions in a database ?Thank you in advance for any advices. |
|
|
jayblaze2
Starting Member
5 Posts |
Posted - 2009-11-13 : 09:25:42
|
| I would creat a table [VERSIONHISTORY]. Create an UPDATE trigger on table [DELIVERY]; during the update to [DELIVERY] insert the old data into [VERSIONHISTORY].i hope this helps"I caught a fleeting glimpseOut of the corner of my eye.I turned to look but it was goneI cannot put my finger on it nowThe child is grown, The dream is gone." (Pink Floyd) |
 |
|
|
|
|
|
how to keep different version of an entity