Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 What is the best normalized design for my scenario
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

41 Posts

Posted - 07/16/2013 :  18:59:00  Show Profile  Reply with Quote
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
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)

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

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 07/17/2013 :  04:33:24  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000