it would be great if someone could assist me with following problem (see attached pic):
I work for a company wich manufactures engines. All engines are planned and sold via 'Projects'. A 'Project' contains generic information about the customer, country, contact person, etc. (I'm ignoring that now)
A 'Project' may contain 2 engines of Type A and 3 of type B.
A 'Project' is not equal an order. What I mean is, a 'Project', at the beginning, is rather a plan. We may loose the Project to a competitor and the engines of the project will never be constructed. The project may become somewhen an order.
Up to now I plan to store every single combination of Project and Enginetype in the table Project_Detail (meaning that Qty is always equal 1). If my project has 2 engines of Type A and 3 of Type B, I'll have 5 records for the Project.
Why? Because -eventually, if we win the Project - I'll have to assign a production date and engine number per engine - which I plan to do in 'Engine'
Once it is built, an engine may be assigned to a different Project (when the time to finalise a Project is out, we may "swap" engines with another project)-
that's why I use that relationship between Project_Detail and Engine.
What do you experts out there think of my design?
Thank you very much in advance!
Sounds like you're over complicating things - especially with every combo of engine!
I reckon you want a design like this
Customers(CustomerID, Company, Telephone, etc)
1 customer can have many projects
Projects(ProjectID, CustomerID, ProjectName, etc)
1 project can have many engines
Engines(EngineID, ProjectID, Type, etc)
FROM Engines e
JOIN EngineHistory h
ON e.EngineID = h.EngineID
AND h.HistoryDate = (
WHERE EngineID = h.EngineID