• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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