Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005

    Help with DB-Design

    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!
    Attached Thumbnails Attached Thumbnails db1.jpg  

  2. #2
    Join Date
    Jan 2007
    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)
    Home | Blog

  3. #3
    Join Date
    May 2005
    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?

  4. #4
    Join Date
    Jan 2007
    Engines(EngineID, Type, etc)
    EngineHistory(EngineID, HistoryDate, ProjectID, Comments, etc)

    then to find out where the engine is right now;
    SELECT	e.EngineID
          , e.Type
          , h.ProjectID
    FROM	Engines e
       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?
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts