Odd relationship behavior

[skingjack2]

Hello,

I have a situation where the expected result of a relationship is odd/wrong post insertion of an additional table into the solution; the data returned now is for a different individual.

The insertion of a join table between 2 tables IMO should have no bearing on the returned data if my understanding is correct (and it may not be).

Below was the previous working relationship info between 3 tables:

clients-> jobs-> cards

clients is related to jobs by client ID, jobs is related to cards by card ID. Card data could be viewed in either the client or jobs tables via a portal.

New table info:

clients->join-> jobs-> cards

clients is related to join by client ID, join is related to jobs by job ID, jobs is related to cards by card ID.

Card data can still be viewed in the client table via portal however the card information it provides is for an entirely different person.

This behaviour is what has me baffled. Any thoughts/suggestions on what might be the problem? All feedback appreciated

TIA

Jack

[Vaughan]

Check that the key values in the join table are correct.

[skingjack2]

Check that the key values in the join table are correct.

Vaughan,

Just confirming, are you saying that the cards table is now to be related through the join table (using a card ID field) rather than via the jobs table that previously worked?

[comment]

Below was the previous working relationship info between 3 tables:

clients-> jobs-> cards

clients is related to jobs by client ID, jobs is related to cards by card ID.

This would suit a situation where one card has many jobs (and each job has only one card). Is this correct?

[skingjack2]

This would suit a situation where one card has many jobs (and each job has only one card). Is this correct?

Hello Comment,

What you describe is correct.

Each card can have multiple jobs

Each client can have multiple cards

Each card can only have 1 client.

Jack

[comment]

I am not sure what exactly "card" means in this context, but if each job can have only one card, and each card can have only one client, then there is no many-to-many relationship here - and no need for a join table:

Clients -< Cards -< Jobs

[skingjack2]

I am not sure what exactly "card" means in this context, but if each job can have only one card, and each card can have only one client, then there is no many-to-many relationship here - and no need for a join table:

Clients -< Cards -< Jobs

Cards as in the context of credit cards ... sorry for the oversight of this information. FYI, though this is rare, each job can have more than 1 credit card i.e. a client pays his/her reservation deposit with 1 card and final

payment with another.

[Vaughan]

So Cards is really a Payments table. I thought it may have been a "job cards" thing.

Yet again, define the problem and not the solution.

[comment]

a client pays his/her reservation deposit with 1 card and final

payment with another.

Well, then the job belongs to the client, not to the card. Can it belong to more than one client?

Unless there are exactly 2 payments for each job (and probably even then) you should have another table for Payments {PaymentID, JobID, ClientID, …}.

If you are storing credit cards info (do you really want to do this?) then I believe it's a one-to-many with clients.