Thank you very much for validating my thinking.
1. I want MarkUpPerc to be unique for a QuoteID for a customer. Therefore, I have added MarkUpPerc, CustomerID and CustName to my QuoteHeaderT Table. Also I have created a separate CustomerT Table where customer details will be stored. These fields are marked in RED in my attached diagram. Could you kindly review my updated structure now.
Quote Header will allow user to enter a MarkUpPerc for an opportunity, for a customer, which will be saved with a unique QuoteID so the user could access anytime later as to how much MarkUp was applied to a particular opportunity for a customer. I also want to add Date to my quote, but not sure if it needs a table.
2. Sorry for confusing you about the opportunities. Let me try to explain in a simpler way. It is CORRECT that OppDetailT has components (as shown in #4). Absolutely right. All these components(line items) shown in #4 apply to ONE opportunity. And I am hoping my DB so far would handle it well.
Each Opportunity has multiple components (line items as shown in #4), BUT there will be a few opportunities whose TOTAL COST (that means COST OF OPPORTUNITY), will be calculated from few other opportunities.
For Example, I am considering an opportunity 'House Construction' to explain this, which is not our business but similar process:-
Please consider I have 4 OppIDs for different opportunities in a Main Opportunity called "Complete House Construction". OppID1 = House Roof, OppID2 = House Floor, OppID3 = House Doors, OppID4 = Windows. All of these opportunities have many line items (components) in OppDetailT Table. For instance, 'OppID3 = House Doors' has components (line items) including door frame, door handle, door latch, etc. (as shown in #4). Similarly OppID1, OppID2, OppID4 also have their components (as in #4), and these opportunities, with their components, are stored with unique OppIds in my database, as we already discussed.
Now, there is an opportunity "OppID5 = Complete House Construction". Cost of this opportunity is calculated from the cost of other opportunities listed above (OppID1,2,3,4). While estimating Opportunity Cost of 'Complete House Construction', user will open House Floor, Door, Roof and few other opportunities (OppID1 2,3,4) to estimate the cost of these opportunities first and then will call the TotalCost of these opportunities to OppID5, where Grand sum of opportunities costs will be performed to get the total cost of OppID5 for a particular QuoteID. Hence, Total cost of house will be calculated by estimating and considering cost of various segments such as door, roof, floor etc. that makes the house, and they are also individual opportunities.
To achieve the above, the Quote Form belonging to OppID5 will look different than the other opportunities. It will not have line items as shown in #4, rather it will have links to other OppIds, with a textbox to call TotalCost from OppID1,2,3,4, and then will perform grand total to get Cost of Pursuing OppID5. I hope this will not be difficult because i will have all Opportunities stored in my db, so it should only be the matter of calling TotalCosts from different OppIDs to a single form?
Hope I haven't confused you more. Attached diagram of tables and opportunity form might make it clearer.
I am very grateful for your help. This matters a lot. Thanks again. Please let me know if there is any confusion.
UPDATED TABLE/RELATIONSHIP
OPPORTUNITY FORM THAT I EXPLAINED ABOVE