Set an equiry system

[L'apprentis]

Hi, I am trying to implement my Database with an enquiry (or quotation) table and I am not sure I am heading in the right direction:

-We use assemblies made of components and the data are held together in the database with a 'Bill of Material' set up.
-A typical assembly is made of 1 or 2 bodies + Fasteners (straps and screws),
-The body price is calculated depending on his weight and an extra costs can be added with extra tooling and machining,

The costumer ask us for a quotation for various number of assemblies and we send back the estimation of the price,

I am really struggling to see if my tables are set right and this is what I came up with (attached rr.zip):
A main Enquiryfile Table is linked to an enquiryDetail Table which would record each individual item information for each Enquiry.
I have also added a Extracost detail table that would record all the Machining and extra pattern making cost for each assembly.
And finally, and that's what I am far to be sure about, a TblChildItemCost where the price of each components would be recorded (for each enquiry detail). The body price is based on his weight but also depend of various market exchange rates, that is the reason why I have created a table for the price of component for each enquiry.
I really hope someone can help me on that one...Thanks in advance.

 

[llkhoutx]

Looks pretty good except that tbChilditemCost should not be related to tbEnquiryDetail, it's only related to tbItem. tbEnquiryDetail does no need an EnquiryDetailID field, it's not related to that table.

Pictures are of relationships are worth a thousand words.

 

[L'apprentis]

Thank you very much for your reply, I understand what you are saying with the link between TblEnquiryDetail an TblChildItemCost which makes sense but it means that I can't keep an history of my enquiries.
If the price of an item is changing over time, the price of assemblies for past enquiries will change as well. Do you think it is possible to maintain the detail value of each enquiry?

 

[llkhoutx]

tbEnquiryDetail is related to tbItem is related to tbChildItemCost.

or Engines have Parts have Costs.

 

[llkhoutx]

You asked

If the price of an item is changing over time, the price of assemblies for past enquiries will change as well. Do you think it is possible to maintain the detail value of each enquiry?

I suppose that was the purpose of your tbenquiryDetail - tbChildrenCost relationship.

I'm working on this ... I'll get back ASAP.

 

[llkhoutx]

I rearranged the view of your relationships as you originally posted in a linear fashion. Clearly, they will maintain the history that you want. Keep them as originall posted, which I assume you did.

bon chance