Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

Job Costing Database Design - need help

I am creating a fairly simple database for an engineering firm to handle their job costing and need some advise on the design.

I have a table for categories and  quotes (and others)

The issue I have is that I want to be able to create a quote that has a cost breakup by category (with the categories listed in the category table). Do I create all the category names as fields in the quote table to store the quoted price for that category, or can I look them up from the category table itself. If I create the category fields in the quote table then I am duplicating data.

Hope this makes sense.
0
TrentSlater
Asked:
TrentSlater
  • 2
1 Solution
 
Patrick MatthewsCommented:
At the very least, you will need something like this (and could well need more)...


tblCustomers
-----------------------------------------------
CustID (PK)
CustName
<others>

tblQuotes
-----------------------------------------------
QuoteID (PK)
CustID (FK)
QuoteDescr

tblCategories
-----------------------------------------------
CategoryID (PK)
CategoryName

tblQuotesCostByCategory
-----------------------------------------------
QuotesCostByCatID (PK)
QuoteID (FK)                     <------ also have unique index on QuoteID + CategoryID
CategoryID (FK)
Amount
0
 
TrentSlaterAuthor Commented:
Thanks for the quick response - i am testing this now with the extra quotes table
0
 
TrentSlaterAuthor Commented:
Works!. Thanks. Easiest 500 points you've ever made.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now