Table relationships

[Cal]

I'm working on a service call database that is dependent
on tracking profit, loss and all activity by each machine
model and serial number.

We have to keep track of the following:

Which piece of equipment is tied to which customer location
Which technicians worked on the equipment
Which workorders are tied to the equipment

I'm trying to determine the relationships my table should
have to an equipment table. Does it have to be related to
the Technician table?

Right now, it's related by CustomerID in the Customer
Table, and by a ternary relationship table including
CustomerID, Manufacturer, Model, SN, Labor ID, PartID and
InvoiceID. I'm guessing I have way too many relationships
there that don't serve a purpose.

I guess what I'm asking is how can I reference a Mfg,
Model and S/N and pull up every documentation regarding it?

I'm thinking too much Time for bed.

 

[Rebecca Riordan]

You are thinking too hard...

You have customers, equipment, technicians and workorders. You might also
have manufacturers, I can't quite tell.

Just take each table in turn and tell yourself what its relationship to each
of the other tables is. For example, customers have equipment. That's
probably a one-to-many, but might be a many-to-many if you swap stuff
around. Customers probably don't have any direct relationship to
technicians at all, but they do have a one-to-many relationship with
workorders.

Once you know what the relationships are, you just create foreign keys in
the appropriate many-side tables, and away you go. You can pull up all the
many-side tables related to equipment using subforms.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[CAL]

I've done that a couple of times already, but I'm still
running into problems. Mostly on how to deal with M-N
relationships. I guess it's because I have Mfg, Model and
SN as attributes to equipment.

I have a form that, if it behaved, would display the
customers, and in a subform, the workorders associated
with them, and in a subform to that, the equipment
associated with the workorder. The problems that I'm
getting include that my subforms displays all equipment
for all workorders, and it doesn't scroll through each
customer, but rather each customer location... which I'm
trying to fix now.

The way I have it now is:

Workorders 1:M Labour
Workorders 1:M Parts
Workorders 1:M Equipment

Customers 1:M Equipment
Customers 1:M Invoice
Customers 1:M Payment
Customers 1:M Workorders

Technician M:N Equipment

Suppliers 1:M Parts



Payment(PaymentID, PaymentAmt, PaymentDate)
Where PaymentID is the primary, and InvoiceID is foreign.

Invoice(InvoiceID, InvoiceAmt, InvoiceDate)
Where InvoiceID is the primary, and WorkorderID is foreign.

Workorder(WorkorderID, WOType, PONumber, DateReceived,
Date Finished, Warranty, Priority)
Where WorkorderID is primary, CustomerID is foreign

Labour(LabourID, RegLabour, OTLabour, NonbillableLabour,
BillingRate,Comment)
Where LabourID is primary, WorkorderID is foreign

Parts(PartID, Quantity, Price, Description)
Where PartID is primary, WorkorderID and SupplierID are
foreign

Supplier(SupplierID, Name, Address, City, State, Zip,
Phone)
Where SupplierID is primary.

Equipment(Mfg, Model, SN, WarrantyExpiration, InstallDate,
MachineType, InstallTech)
Where SN is primary and CustomerID and WorkorderID are
foreign

Employee(EmployeeID, FirstName, LastName, Title, Email,
Email2, Phone, Phone2)
Where EmployeeID is primary and WorkorderID is foreign

Customer(CustomerID, Company, CreditHold)
Where CustomerID is primary

CustomerContact(CustomerID, ContactFirst,ContactLast,
Contact Phone)
Where ContactFirst,ContactLast are primary and customerID
is foreign

CustomerLocation(LocationID,
CustomerID,Address,City,State,Zip,Phone,Fax)
Where LocationID is primary and CustomerId is foreign


Yeah.. so time for more coffee.

 

[Rebecca Riordan]

Sorry for the delay in replying to this...

If you haven't yet got this working and want to send me a LITTLE sample
(only a couple of records and the forms that are causing problems), I'll
have a look at it for you. Sounds like you just don't have the parent-child
links on the forms quite right, and that's a lot easier to see than to
explain <g>

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress