Table Structure

[Double A]

I have created a database to track work orders for a landscape company. Each
customer can have many work orders. Each work order can have multiple parts
to the job. My table structure is as follows:

tblCustomerInfo
---------------------
CustomerNumber AutoNumber
Name Text
Address Text
City Text
State Text
Zip Text

tblWorkOrder
---------------------
WorkOrderNumber AutoNumber
EstimateDate Date/Time
CustomerID Number - Combobox to choose customer from dropdown

tblWorkLineItem
----------------------
WorkOrderNumber Number
CustomerID Number
LineItemNumber AutoNumber
Category Text - Combobox pointing to a look up table of
categories
Cost Currency


The data seems robust when entered using the forms that I have set up.
However, in using the database, it has been possible for the data in the
table to show one work order associated to 2 different customers. How can I
prevent this from occurring? I see this when I look in the WorkLineItem
table. I need to allow duplicates for both the Workorder and customer ID
because each record is one part of the workorder.

I am not sure if this question is clear. Please respond with questions.

Thanks.

 

[George]

Well,

First, is better to use a better primary key for your customers table.

Then, the workorders table seems to be OK if it doesn't matter the actual
serial number of it (if is like an invoice, then the primary key must not be
autonumber, since will not be so compact - if you cancel lets say a workorder
by escape key then and this workorder number is 3, then the next one will be
numbered 4 - you will not have the number 3.)

Now, about your tblWorkLineItem there is no need to include the customer ID.
Also, if within a workorder the categorypart would be allowed once for each
workorder then change the primary keys to both WorkorderID and partID.

Finally you will have One customer with Many Workorders, and workorders will
have many parts.

Hope this helps

GeorgeCY


Ο χÏήστης "Double A" έγγÏαψε:

 

[Fred]

If I may emphasize on point which George just made a low key reference to,
having Customer ID in your line item table is probably a bad idea, and and
using it in a relationship would almost certainly cause problems.