Need help getting my relationship right

[BukHix]

I need some help setting up the relationship for this database in a VB.Net/Access project I am working on. Currently the relationship looks like this:

Now I need to add one more table that will have a one to many relationship with the (history?) table:

The history table has the JobNumber, which is basicaly what hooks everthing together in my app. Each Jobnumber could have several parts tracking records though.

Any suggestions?

 

[WayneRyan]

Buk,

Your initial Relationship shows that you have some customers; each
having a history of vehicles.

Some number of Customers have ...

Some number of History Entries ... (all involving vehicles)

Each History Entry has a VIN, Make, Model, etc.

This app obviously revolves around people and the vehicles that
they are associated with.

Now, you add a PTRACK table and want to track it by JobNumber?
Get real!

Just kidding. But the initial app was customer centric, dealing with
the customer's relationship with some number of vehicles. Now that
they are ordering Parts, you want to track it to a JobOrder Number?

Somewhere in all of this is fact that the Vehicles/Parts belong to
two entities; Customers and JobNumbers. It's a many-to-many
relationship.

Can Customers have multiple JobNumbers? Or vice-versa?
What is a JobNumber? Obviously it's pretty important.

Wayne

 

[Pat Hartman]

You also don't have referential integrety enforced. Go back to the relationships and check the enforce RI box. If your business rules allow a customer with history to be deleted, also check the cascade delete box in both relationships.

 

[BukHix]

Yes, unfortunately for me, someone else usually sets up the databases I work with. So I have never been forced to learn the correct way of doing it. I know I should take the time to learn but I have spent the last couple of years trying to wrap my mind around VB.Net, Crystal Reports and the concept of OOP (the mind is pretty much mush now so there is not much to work with anymore).

Anyway ......

But the initial app was customer centric, dealing with the customer's relationship with some number of vehicles. Now that they are ordering Parts, you want to track it to a JobOrder Number?

The initial app is a commercial application that I am trying to enhance slightly. In the original app they don't track parts. This is what I am trying to do.

Our business tracks everything by the JobNumber, which is why my add-on does also. Although as I think about it now that is not necessary because I can tie it together with query view.

Somewhere in all of this is fact that the Vehicles/Parts belong to two entities; Customers and JobNumbers. It's a many-to-many relationship.

For the purpose of the App I am working on I only need to track the status of the parts for the life of a job, which usually last for two to three weeks from beginning to end. After that the parts status records will just be deleted.

Can Customers have multiple JobNumbers? Or vice-versa? What is a JobNumber? Obviously it's pretty important.

Yes customers can have multiple job numbers but each would be a separate job with separate parts to track.

Basically this is what I am trying to do. There are 3 to 4 people who answer the phone here. I want to set up an app where when a customer calls asking about the status of their car anyone of the people can punch in the job number and check to see if there are any parts back orders or other parts related problems that would hold up the scheduled time of departure. It happens quite a bit.

Again the particular information I am working with is temporary in nature, will change frequently throughout a day and will be deleted as soon as the repair is completed.

How should I change my Ptrack table to fit in with the other tables? Once the relationship is correct I can connect everything with a query.

 

[BukHix]

I got it now. Thanks!