Separate or common forms to manage Offers and Orders database? (1 Viewer)

[gringope24]

I made some changes in database design. I want to discuss it with you because I still missing the idea how to solve my problem.
Please fallow a steps of possible scenario of user:
1. Create a statement which always at the beginning is Offer (StatementType).
2. Input Groups, then input Items and other information. No problem.
3. Access generate the Offer Letter and I am sending it to Customers.
3. Hopefully, one of the Customers want to buy everything from my Offer.
4. So I change a flag (Change StatementType to Order), input data in tblOrderData. Everything is fine!
but
5. Suddenly customer call me that he wants to make changes in Order, for example: modify quantity of specific Item, add some Item in different Group and delete one of the Groups with its Items Completely. I can modify all of this things, but I want to also keep the history what was offered in the beginning!

To handle the issue from point 5, I thought about solution presented below.
I make pairs in tblStatementGroups and in tblGroupsItems and give them specific code to know what is their history. For example when Group and Item go through the whole process without any changes then it has code 'Offered and Ordered'. But when I change the quantity of specific Items it technically means that I create a new record in tblItems by duplicating Item and change only quantity, next step is to relate new record with accurate Group in tblGroupsItems, now it has code 'Order Only'. The original record in tblGroupsItems need to change its code 'Offered and Ordered' to 'Offer Only'. This situation is presented in below example:

There is a Group where GroupID=1 and 3 x Items where ItemID=5,6,7:
Group 1, Item 5, Offered and Ordered
Group 1, Item 6, Offered and Ordered
Group 1, Item 7, Offered and Ordered

I change qty in Item 5, so now it is tblGroupsItems we see:
Group 1, Item 5, Offer Only (change code)
Group 1, Item 6, Offered and Ordered
Group 1, Item 7, Offered and Ordered
Group 1, Item 8, Order Only (new record)

After some time I need to change data in GroupID=1, so I create GroupID=2, and it also make fallowing changes in tblGroupsItems:
Group 1, Item 5, Offer Only (change code)
Group 1, Item 6, Offer Only (change code)
Group 1, Item 7, Offer Only (change code)
Group 2, Item 8, Order Only (change only GroupId, because Item was already created as Order Only)
Group 2, Item 6, Order Only (new record)
Group 2, Item 7, Order Only (new record)

The disadvantage that I see here is also that both tblGroups and tblItems need to have code fields. If not, how Access will now if it is allowed to change data within a record (if code is 'Offer Only' or 'Order Only') or new record needs to be created (if code is 'Offered and Ordered').
Now I think that maybe code field shouldn't exist in tblGroupsItems and it should be present in tblItems and tblGroups. What do you think? Query could help me to filter which items and Groups are dedicated for 'Offer Only', 'Order Only' or 'Offered and Ordered'.

I am not sure if my solution is optimal. Maybe you have different view and opinion to this design, to ensure its good performance.

 

[jdraw]

Have you run some test data and test scenarios against your model to ensure you can retrieve the info you need? See stump the model.

 

[gringope24]

I haven't made tests - not all things are ready yet (no queries, no forms). At this stage I wanted get an advice if I am going in right direction and If don't make basic mistakes in design.
I understand your post as "if it will work according to your expactations, then it is fine" .

 

[jdraw]

You don't need queries nor forms in order to test your model with some sample scenarios.
Just some test data and test scenarios, and some idea of what you expect as a result of that scenario.
Could be a list of People, some addresses, a mock up report.....

 

[gringope24]

Before I perform tests I am also thinking about some improvement in design:
1. I would make tblOfferDetails and tblStatment as one table. Previously I though that It can be many versions of Offers for one Statement, but now i think that I will create new versions simply by duplicating the whole Statement and related records (I am not expecting that user will be creating many versions, therefore a disk space should not be problem). Is it make sense?
2. One record from tblStatements can have only one record form tblOrderDetails. Is it good to keep seperate tables and relation 1-to-1 between these tables or it is better to make one big table tblStatment + tblOfferDetails + tblOrderDetails and keep fields coming from tblOrderDetails null (or empty) till Offer become Order?