Help with DB-Design

payaso · #1 (**permalink**) 07-16-07, 09:28

Hi all folks,

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!

gvee · #2 (**permalink**) 07-16-07, 10:02

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)

payaso · #3 (**permalink**) 07-17-07, 04:24

Thank you georgev very much for your reply.

The problem is that I need to be able to change one Engine
from one Project to another. And keep track of those changes.
This is what I wanted to do in Project_Detail.

A single Engine may be 'ordered' over a Project at a certain price
to be later changed to another Project to a different price.

To do that the engine has to be 'booked back' in one Project and then
'booked' in the new project

I hope I am being clear

Can someone help me further?

gvee · #4 (**permalink**) 07-17-07, 04:32

Engines(EngineID, Type, etc)
EngineHistory(EngineID, HistoryDate, ProjectID, Comments, etc)

then to find out where the engine is right now;

Code:

SELECT	e.EngineID
      , e.Type
      , h.ProjectID
FROM	Engines e
  LEFT
   JOIN EngineHistory h
     ON e.EngineID = h.EngineID
    AND h.HistoryDate = (
		SELECT	Max(HistoryDate)
		FROM	EngineHistory
		WHERE	EngineID = h.EngineID
		)

How's that for ye?

Internet Services

Web Development

Digital Marketing

Consumer Tech