Separate or common forms to manage Offers and Orders database?

[gringope24]

Dear all,
I am developing access database to manage Offers and Orders in my company (add items, calcutate prices and to automate process of generation reports, purchase orders and other documents). I have already create a part dedicated only for Offers part.
The simplify structure of already created part is:
tblOffers
tblOfferGroups (related with tblOffers)
tblOffersItems (related with tblOfferGroups)

Almost identical structure will be for Orders part:
tblOrders
tblOrderGroups (related with tblOrders)
tblOrdersItems (related with tblOrderItems)

When Offer become an Order, the data from tblOffer will be transferred to tblOrder (other information from related tables will be also tranferred accordingly). It also means that when Offer become an Order there will be some new information or information will be different in tblOrders than in tblOffer.

My concern now is: should I create new form to menage tblOrders and other related tables or can I make universal (common) form for Offers and also for Orders? There will not be much differences between layout of the forms but it would create some problems:
- rowsource will be assigned dynamically
- in database is not only one form but about 6-7 forms, therefore dynamic rowsource must be used also for other forms (i expect some problems with proper functionality then)
- it will be necessary to hide or to show some txtboxes and maybe other elements depending if I work with Offer or with Order
- and many more...

On the other hand I am worry that when I duplicate the forms the size of the front end part will grow very much. Of course I have already checked what will happen if I copy&paste 2 times already existing forms and save the accdb (I have not devided the db to front-end and back-end yet). Surprisingly the size of accdb file has not changes. I am not sure if it is correct behaviour...

To sum up:
- is it good approach to duplicate the forms and have separate forms to manage offers and orders?
- which objects "weights the most" in accdb file? And how I should create front end to keep it us small as possible and parallely to keep the best performance?

Hope that my concern is clear for you and that you will share your thoughs and experience! Keep in touch!

 

[plog]

Agree with Gizmo.

Almost identical structure will be for Orders part..
When Offer become an Order, the data from tblOffer will be transferred to tblOrder...

That's not how databases are to work. You don't store data in object names and you don't move data. You are storing the type (order or offer) in the table name by having 2 tables. Making that field eliminates the issue you have posted about and saves you about 7 future issues.

 

[jdraw]

Agree -clear description of the "business" involved is required for readers to offer focused advice.
For consideration:
Data model Barry Williams' Customers and Orders
Abstracting Quote/Order/Invoice to Statement

 

[gringope24]

Thanks for all suggestion. Please find below my further explanations:

Basic assumptions are:
- the DB will be successor of old database for which source code and structure is unknown and cannot be recreated;
- the DB must respond to the real needs and habits of users and also specific profile of the business, which is Energy Equipment and Services;
- This is not basic purchasing of coffee or a book with pre-defined products. There are no products with fix parameters and price. Therefore, every time User will have to input information about Items manually. It is fine for all users, because every time the price and the description of the product is different (somehow tailor-made). Offer must be divided into Groups, because each Group is different technical equipment.
- Offer is preparing by User (sale person) and when finish it is send to Customer (or even many customers) and being discussed and negotiating. Customer can order all Groups from the Offer or only the ones which are attractive for him.
- After customer will send Purchase Order, then User can transfer the whole Offer to become the Order. Morover, he must adjust and fill some new information (for example: requested delivery date for each item, delivery dates and etc - this information are not know at the stage of the offer)and sometimes maybe delete some Items or Groups. In the end, he is sending Order Calculation which has to be accepted acc. to internal procedures, after that, next steps can be done with subsuppliers.
- When Offer become Order, than field Status is Change to Win.
- Taking above into consideration - this is why i am considering to have separate tables for Orders. Moreover, in my opinion it will bring more clarity in the structure of DB. TblOrder will refer to tblOffer using foreign key.
- The basic structure of the offer with main fields are presented below.

Offer (IdOfferPK, OfferNumber, OfferVariant, OfferName, End_User, Sale, ProjectManager, ProductManager, Commercial, Warranty, PaymentConditionFK, IncotermFK, DateOfferValidity, DatePriceValidity, DateInsert, DateLastUpdate, Status)
|
Group 1 (IdOfferGroupPK, OfferFK, FinancialFactors, LogisticFactors, NegotiationFactors, Risk, Other1, Other2)
|
---Item 11 ( IdOfferItemsPK, GroupFK, ProductType, RatedVoltage, RatedCurrent, RatedShortCircuitCurrent, SpecialParameters, Delivery time Quantity, UnitPrice)
|
---Item 12 (...)
|
---Item 13 (...)
Group 2 (...)
|
---Item 21 (...)
|
---Item 22 (...)
|
---Item 23 (...)

The structure of Orders will be similar. but some field will be added or modified by User when all details of Order will be known from Customer. The relations between Offers, Orders and Customers are separate issue and is not mentioned here.

 

[Minty]

Simply have all the fields you need for an Order in your Offer table.

Instead of Status Change, consider using OrderedDate as a flag for your Status change.
Two Forms one that allows Offer entry, and a similar one that allows the additional Order fields to be completed, that form only displays records that have an Ordered Date completed.

 

[gringope24]

- Regarding design: I don't want situation that when I change something in my Order (or OrderGroup, or OrderItem), then change will be also applied to Offer (or OfferGroup, or OfferItem). I want to keep data in Offer unchanched. I need funcionality to modify Order details independently to Offer.
- Regarding the forms: how can i check how duplicating the forms influence on the size of my front end part? Or maybe I should not care about the numbers of forms and create it as much as I need?

 

[gringope24]

OK Uncle Gizmo - thank you for your helpy hand, I will try to describe everything step by step.

- Offer/Order consists of Group
- Groups consist of Items;
- Offer and Order can have versions from 0 to many (new version is created when there is a major change in the scope of the Offer or Order);
- Offer/Order is generally describe by Name and Internal number (different format/range for Offers and Orders);
- Groups is described by financial factors;
- Items are described by Technical Parameters, Quantity, delivery time and Unit Price. Items are very unique, therefore I don't see possibility that there is finete number of them. Every time User should put them by him own;
- Unit Price for customer is the calculated as sum of Factors multiply by price from sub-supplier;
- Offer can be send for many Customers;
- Order has only one Customer;
- Changes made in order should not influence on Historical data from Offer and vice-versa (for example user can modify financial factors or quantity of items within the Order but information in Offer Part be keep as in the beginning);
- changes made in one of the version of Offer/Order should not influnce on its previous versions

The final view for Customer of Offer letter should be similar to below:

Offer 123456 (version 0):

Group 1 (Equipment 1)

Total price for a Group 1: 1000,00​

Pos.	Discription	Quantity	Unit Price	Total Price	Delivery time (months)
1.	Device 1	1	100	100	3
2.	Device 2	3	200	600	4
3.	Device 3	2	150	300	2

Group 2 (Equipment 2)

Total price for a Group 2: 1700,00​

Pos.	Discription	Quantity	Unit Price	Total Price	Delivery time (months)
1.	Device 4	4	50	200	1
2.	Device 5	5	100	500	4
3.	Device 6	4	250	1000	2

Total Offer Price: 2700,00

 

[gringope24]

What is this? Please explain.

Offer/Order -> It means that aspect which I am describing is referring to both instances: Offer and Order.

Moreover, I came to conclusion that it will be better for db to create tblStatements and for every entity I will provide StatementType (Offer or Order). It will simplify the structure.
Nevertheless, I think that If i want to keep history of Offers and allow to modify Orders values indepedently, than during changing Offer to Order, it is no other way than duplicate the row in tblStatement and change value in StatementType column. Records in related tables must be also duplicated and connected with the new created record in tblStatements.
Let me know if you have any different vision for it.

EDIT:
In post #10 I mentioned tables to visualize the idea of Groups and Items as an output for Customer.
Please note that Offer and Order are described also by more values like responsible people, dates and etc, but this fields are quite easy to relate each other and I didn't want to overhelmed you by too many details.

 

[gringope24]

No problem. With patience I am ready for every level of details. It takes me some time to describe this model as clear as possible:

Offer (or Quote or Quotation) is supplied in reply to an inquiry. It has form of letter (in my company it is simply signed PDF ), which can be addressed for many Customers.
It works like this way: End user is announcing a Tender, in which many Companies (my Customers) can participate. Requirements and scope of supply is given in Tender specification. I am the sub-supplier of this Companies as a Manufacturer, they do the rest of the works like installation , civil works, constructions and etc. I am receiving inquires from many Companies. They are all asking for the same.
When I receive the first inquire I want to create a record in database, which will have status of the 'Offer'. There, I put some general information like Name of Project, submit date, responsible people, delivery conditions, warranty conditions and etc.
Then, I want to create a Group within the Offer which will held information of financial and logistic factors which will be used to calculate the price for customer. These factors are mostly standarized, but I also would like to be able to change it to custom values. There can be 1 or many groups in the Offer.
Next step is to enter Items in the Group. I add type of the product, technical parameters, detailed description, quantity and my internal price. Items are not standarized. It can be many, many configurations of the Product I don't see possibility to organized it, for every project it is different story.
There can be 1 or many Items within the Group. Group is like container for Items. And offer is like container for a Groups.

When I receive inquire for another Company, then I want to relate this Customer to this Offer.

Based on the input data I want to generate Offer Letter which will be ready to send to Customers.

After some time End User announcing which Company (which my Customer) win the Tender. Later, this Customer make some negotiationswith me and therefore it might be necceseary to prepare new version of the same Offer, but with better conditions and prices - so i need to adjust previously input information. The Previous version of the Offer should stay in database for historic reasons.

If the Company has decided to choose me as their sub-supplier, than they provide me an Order, in form of the letter, where they inform, that they purchasing some stuff from me. The scope of supply in the Order may not include every groups or items from my last version of Offer, because they choose the most attractive items from different sub-suppliers.

Anyway, I need to change status of the main record in database from Offer to Order and modify the scope of supply, prices and quantities and etc. according to Customer's Order.
Please remember that I want to keep the last version of Offer unchanged as archive.

The next step is to generate calculations and confirmations based on information in database, but I assume it as a next step of my work, which should be quite smooth when database will be working fine.

 

[gringope24]

Taking into consideration my above description I created db structure. Can you please advise if it make sense?

 

[plog]

Looking at this I have to agree with myself when I agreed with Gizmo:

a better solution is to add an extra field to the original table "tblOffers" to act as a flag to identify what condition the original item was in, without sending it into a new table.

tblOffers and tblOrders should be one table. You are essentially storing a field value ( Type) in the table name (Offer or Order). Store that in a field within the table.

 

[gringope24]

tblOffers and tblOrders should be one table. You are essentially storing a field value ( Type) in the table name (Offer or Order). Store that in a field within the table.

- would it be correct to store these information in tblStatement or should i create separate table tblOffersOrders?

Another thing:
- 1 Offer can have many customers, and 1 Order can have 1 Customer. For table tblOffersOrders the relation for tblCustomer will be always 1 to many. Should I still keep tblsOffersCustomers and tblOrderCustomer and relate them with new tblOffersOrders or modified tblStatements?

 

[plog]

I'm looking closer now and I see a few more things.

1. tblStatementTypes is connected to too many tables. Its in tblStatements, tblStatementGroups, tblOffers and tblOrders. My guess is its data should only relate to tblStatements. However, you bump up against another rule--a table with only 1 real field of data (autonumbers don't count as real data) shouldn't exist. Instead of a table for tblStatementTypes you should just have a text field for StatementType in whatever table its data belongs to--no need for a whole table and storing just the ID--just store the value.

2. You've got a few 1-1 relationships--is that correct? Generally those are unneeded--you simply put all the fields together in one table. If tblStatements is in a 1-1 with both Offers and Orders then all those fields should be in 1 table. But is that true? Does a statement only cover 1 offer/order?

3. I would combine tblOffersCustomers and tblOrderCustomer into 1 table as well.

 

[gringope24]

Ok, I took me some time for answer but I had technical problem with publushing a post with links to websites. It seems that my status doesn't allow me to do this. Nevetheless, I will try to to continue discussion.

Ad. 1. Before I Start to do anything I just would like to clarify the rule that a table with only one filed shouldn't exist - I saw many designs where 1 table store for example codes of statuses (colors or or referential information).
On databaseanswers.org there are many examples like (see: databaseanswers.org/data_models/index.htm): Product Characteristics (table Ref_Colors) or Product Servicing (table Ref_Machines_Types) or even in post #4 by jdraw.
I am interested in general approach fo these cases because in my opinion, having type values or status values in separate table is a part of normalization.

Ad 2. - 3. I am now in re-design process taking into consideration your ideas - it will take me some time.

 

[gemma-the-husky]

You could have tables with one field.

Your status table is a good example. Adding a status table restricts status entry to values in the table, and allows new values to be added without requiring programming changes. In practice you would probably have two columns. A status value integer field, and a corresponding status text field, so you can edit the text descriptions without causing cascading updates everywhere. Also a numeric value is cheaper (in computer terms) and easier to use than a string. The same applies to most description management lists. I think it's worth adding the numeric designation.

 

[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?