do I have too many tables or can some be combined

[Chris]

Working on a database to track "quotes" created for a project, "orders"
created for a project and 'change orders" created for orders of the project

the quote, order and change order whilst different purpose all have the same
format. They each consist of the main table ie quote,order and change order
with linked tables for expenses, labour and details I therefore have the
follwoing tables

Projecttable (ProjectID key)
Quotetable (QuuteID key)
Quoteexpenses (Quoteexpid key)
Quototelabour (Quotelabid key)
Quotedetails (Quotedetailsid key)
Ordertable (OrderID key)
Orderexpenses (Orderexpid key)
Orderlabour (Orderlabid key)
Orderdetails (Orderdetailsid key)
ChangeOrdertable (ChangeOrderID key)
ChangeOrderexpenses (ChangeOrderexpid key)
ChangeOrderlabour (ChangeOrderlabid key)
ChangeOrderdetails (ChangeOrderdetailsid key)

Relationships
Projecttalbe linked to Quote and Order table by ProjectID
Quotetable linked to Quotelabour, Quoteexpenses and Quotedetails by QuoteID
Ordertable linked to Orderlabure, Orderexpenses, Orderdetails and
ChangeOrdertable by OrderID
ChangeOrdertable linked to ChangeOrderlabure, ChangeOrderexpenses nad
ChangeOrderdetails by ChangeOrderID

Aside from the key fields the data is otherwise all the same and being
supplied from the same other linked tables such as Products, expense type
etc.

Question, Is it correct to have a differnent table for each of the expenses,
labour and details or should I have one table for each and have three
differenet key fields to identify them with the corresponding tables.

Hope someone can clarify
Thanks
Chris

 

[Jeff Boyce]

Chris

It appears you've embedded data in the names of your tables (and this is not
a good thing <g>).

Rather than creating a new (set of) table(s) for each category, use a single
table and add a (single) field for categorization. This way, you will have
a record that indicates Expense, Labour, and ..., and a Category = Quote (or
Order, or ...). One row for each, each with its Category.

Since each of these rows is related to a Project, consider creating a
Project table (with ProjectID and details specific to the project), then
using the ProjectID to indicate which project the Expense, Labour, ...
Category row belongs to.

Good luck!

Jeff Boyce
<Access MVP>

 

[Chris]

Thanks for the pointers. Because the expenses relate to either the order,
quote or change order believe I need to structure the expense table etc with
following fields and use the same format for Labour and details if this is
OK

ExpeneseID (Primary key)
OrderID
QuoteID
ChangeOrderID

There would then be one table with each record having an entry in the
corresponding field for QuotesID, OrderID or ChangeOrderID. Tried to use a
category and ID field but seemed more complicated.

It is the Order and Quote the relate directly to the Project. Expenses,
labour, Details relate to the quote, order or change order. Does that make
sense

Thanks
Chris