Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Table Structure, Need expert advice?

    I have asked a similar question on SQLTemp.com, but I thought I would ask here in case there are people here that don't visit SQLTeam and can help me.

    I have to tables:
    Code:
    tbSiteworkPriceList
    ItemID | Descr     | Material | Labour | Travel | Boarding
    -----------------------------------------------------
    1        | Finishing | 0.25      | 28       | 42      | 65
    
    tbSiteworkCostTypes
    CostTypeID | Descr
    -------------------
    1               | Materials
    2               | Labour 
    3               | Travel
    4               | Boarding
    Now, the tabels above are a sitework price list for an estimating package. The tbSitworkCostTypes table is used to trace the value of the particular item back to an account after the item is added to a takeoff. My questions are:
    1) How should I tie each items variable (Material, Labour, Travel, Boarding) to its appropriate cost type?
    2) Should I divide the table tbSiteworkPriceList above into 2 tables as shown below?

    Code:
    tbSitworkPriceList
    ItemID | Descr 
    1        | Finishing
    
    tbSiteworkCostItems
    fkItemID | fkCostTypeID | Price
    -------------------------------------------
    1           | 1                 | 0.25
    1           | 2                 | 28
    1           | 3                 | 42
    1           | 4                 | 65
    This seems to be a little better in the point of view that each item variable is linked to the appropriate cost item by joining the tables tbSiteworkCostItems to tbSiteworkCostTypes. They have to be linked because after the takeoff is generated, the dollar values have to be imported into an accounting system where the account code and cost type code are determined by the "CostTypeID". Any thoughts?

    Mike B

  2. #2
    Join Date
    Dec 2004
    Posts
    46
    depends on your list of cost type changes frequently or expanding, then 2nd is propably better, if not just do it the easy way.

Posting Permissions

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