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!

 

[Uncle Gizmo]

Almost identical structure will be for Orders part:

In my experience when you have a table "tblOffers" which represents an "Offer" and with one small change it becomes another thing, an "Order" but it's basically the same data. Then it's not the case of shifting all the data into a new Orders table "tblOrders" 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.

I think you should go back several steps and explain what you are doing, what your business involves, you might be barking up the wrong tree. You could well be creating a lot of unnecessary work for yourself

 

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

 

[Uncle Gizmo]

- 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.

It appears to me that you are not grasping the essence of the advice you are being given. In these cases, which are common, where the OP (OP is you) is stuck in a mindset of how their database should be constructed and unable to to take on board advice, then it is best that we start from first principles.

Firstly it is possible that you are on the right track and the way you think it should be done is the correct way. I don't know enough about your database and your work Processes to be able to judge that, but my instinct is that what you are trying to do is wrong, with regard to database construction.

So now the task falls to you to explain your working processes in detail, explain what you do as if you were talking to a child, we will not be offended!

You could say something like the operator received a telephone call from so-and-so and from the conversation gathers the following information. Things like this are very helpful in trying to suss out the best way to approach your problem.

Another approach is to have paper forms that your operatives fill-in, indeed there must be something that existed before the computer system. You've probably used paper forms, emails and spreadsheets. All the information gathered in this manner will be useful in setting out the correct way to handle your data.

 

[Uncle Gizmo]

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

You must avoid having duplicate forms. If you have lots of forms which are basically the same but may have a different title, or some have one control hidden and another form has different set of controls hidden, then your best approach is to have one form and change its format, "hiding and unhiding controls" with VBA code. If you start making multiple copies of a similar form then it will become a headache very quickly.

 

[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

 

[Uncle Gizmo]

Offer/Order

What is this? Please explain.

 

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

 

[Uncle Gizmo]

explain what you do as if you were talking to a child, we will not be offended!

Please note the above.

Now please explain Offer/Order.

 

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