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

    Unanswered: Table design ? What to do?

    I am developing an estimating application and at the "quantity takeoff" level, all products/equipment/sub contracting/etc.... end up on the same table because of the common elements:

    Item | Quantity | Unit Price | SubTotal | Total
    Now, since the unit price of a product is not determined from the same tables as the unit price of equipment rentals or sub contract work, etc...
    how do I direct the attention to the appropriate table when trying to obtain the unit price or edit the unit price etc...

    If my question is not clear, let me know, I will try to clarify.

    Mike B

  2. #2
    Join Date
    Oct 2003
    Posts
    268
    How do you "direct the attention"? Yes, clarifying specifically what you want to do would help.

    I suspect what you want is a user defined function that would retrieve the unit price based on the item "type" and ID.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "...unit price of a product is not determined from the same tables as the unit price of equipment rentals or sub contract work..."


    ...maybe it should be. Maybe they should all be stored in a common table with a field to specify the type of transaction.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by blindman
    "...unit price of a product is not determined from the same tables as the unit price of equipment rentals or sub contract work..."


    ...maybe it should be. Maybe they should all be stored in a common table with a field to specify the type of transaction.
    Unfortunately it cannot be. There are many factors stored on other tables and price lists that influence the price of the product; and there is only a single vendor/price list for any additional costing items.

    E.G.,:
    Code:
    tbMaterials
    MaterialID | Material | Price | Units
    ----------------------------------------
    1              | Stone    | 15.00| Tonne
    2              | Sand     | 10.00| Tonne
    3              | Admix   | 1.50  | L
    
    tbProducts
    ProductID | Product 
    -----------------------
    1001        | Solid Slab
    
    tbProductTemplates
    TemplateID | fkProductID | Description
    ----------------------------------------------
    1                | 1001           | 8" Thick Solid Slab
    
    tbTemplateMaterials
    fkTemplateID | fkMaterialID | Quantity | Units
    ------------------------------------------------------
    1                   | 1                 | 1580      | lbs
    1                   | 2                 | 900        | lbs
    1                   | 3                 | 5           | fl.oz.
    The above is the basic table structure that generates the cost of products. In the take off, the user will select the template id, the cost of
    the product is determined based on a quantity entered inthe takeoff. The template materials table you see above is the can generate the cost / unit mesure. If the product is costed by Square Feet, Linear Feet, etc....

    The price list for everything else isn't nearly this intricate:

    Code:
    tbCostTable
    EntryID | Company | CategoryID | Price | ......
    Any suggestions.

    Mike B

Posting Permissions

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