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.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 What is the best normalized design for my scenario

Author  Topic 

madlo
Starting Member

41 Posts

Posted - 2013-07-16 : 18:59:00
I have a buyer table and a seller table
The seller creates a proposal what he wants to sell
The buyer can accept or create a counter proposal
The seller can then accept this or create a counter proposal to this
etc
Until proposal is accepted by both parties

A counter proposal can change multiple fields amount,description,frequency, type, date, duration

I 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 I
1. 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 details
3. 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 this

3 tables - one for buyer, one for seller and one for proposal
proposal will have buyer/seller ids as foreign key
each 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 can
1. find latest proposal by look at Active=1 and also find Initiator by looking at InitiatedBy id value
2. Use original proposal to find its master and do recursive logic to go all way up until the first proposal

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -