Jump to content

Odd relationship behavior


skingjack2
 Share

This topic is 4326 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 4326 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.