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 |
|
madlo
Starting Member
41 Posts |
 Posted - 2013-07-16 : 18:59:00
|
| I have a buyer table and a seller tableThe seller creates a proposal what he wants to sellThe buyer can accept or create a counter proposalThe seller can then accept this or create a counter proposal to thisetcUntil proposal is accepted by both partiesA counter proposal can change multiple fields amount,description,frequency, type, date, durationI am not sure what the best database design for this will be I would as I want to design it correctly.I'd like to follow best practice for this design and 3rd normal form but each time I think of a design I am not happy with it.So do I1. put the proposal in one table and all the counter proposals in another table called counterproposal (1 to many)2. Have a master table and details table so the master proposal has a current valid id detail field that shows which proposal is the current valid proposal in the proposal details3. only have one table with all the proposals and counter proposals together and I know the last proposal is the active proposal (or there is a flag column in this table to show which proposal is the current active proposal)Also should I have a table AcceptedProposal which has the same columns in the Proposal table and once a proposal is chosen it will insert the selected table data from the proposal table or rather must I just have have a boolean flag in my proposal table called IsAccepted to inidcate the accepted proposal |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-17 : 04:33:24
|
| I would do it like this3 tables - one for buyer, one for seller and one for proposalproposal will have buyer/seller ids as foreign keyeach proposal record will indicate a proposal with a date to indicate time when it was raised along with other data items. Initiated By column will include id of buyer/seller who initiated it. A bit field Active will indicate current active proposal. There will be a master proposal id which will indicate the original proposal for which counter was raised (by default when a proposal is created first you may make original porposal id same as proposal id or set it to NULL)so any time you can1. find latest proposal by look at Active=1 and also find Initiator by looking at InitiatedBy id value2. Use original proposal to find its master and do recursive logic to go all way up until the first proposal------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
What is the best normalized design for my scenario