Posted - 07/16/2013 : 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
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