Solved

Job Costing Database Design - need help

Posted on 2011-02-18
3
451 Views
Last Modified: 2012-05-11
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
Comment
Question by:TrentSlater
  • 2
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
Comment Utility
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
 

Author Comment

by:TrentSlater
Comment Utility
Thanks for the quick response - i am testing this now with the extra quotes table
0
 

Author Comment

by:TrentSlater
Comment Utility
Works!. Thanks. Easiest 500 points you've ever made.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now