Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    7

    Database Design trouble

    Hello,

    Im having trouble with a database design.

    I have a number of different expenditure types (Labour, Plant, Materials). I have split these into different tables e.g. Labour: LabourID, Description, GradeID, Rate. Materials: MaterialID, Description, UnitCost, UOM. Etc.

    I then want to have an Expenditure table where I store instances of these expenditure types against tasks, e.g. Task, ExpenditureTypeID (from labour/materials/plant).

    The problem I have is that I have split the expenditures up into different tables and I then want to store the instances in one table. Is this possible?

    Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    I wouldn't mind betting that each expenditure type could be stored thus

    Exp_ID
    Exp_Name
    Exp_Cost/p unit
    Unit Name

    Exp_Cost/p unit is otherwise known as rate, Hourly Rate etc.
    So Labour might be

    AUTONUMB (Say: 00435)
    "Labour"
    6.50
    "Hour"

    Then when you have an expenditure it simple is
    Cost_ID
    Cost_Decription
    Cost_Qnty
    FK_Exp_ID

    so a five hour job might be
    AUTONUMBER (Say: 09578)
    "Fixing the sink"
    5
    *00435

    The total would never be stored but could be calculated at run time

    Total = [Cost_Qnty] * [Exp_Cost/p unit]

    You can now use data to define as many different expenditures as you like. If you want you can allow users to add more of their own.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

Posting Permissions

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