skingjack2 Posted April 2, 2011 Share Posted April 2, 2011 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 More sharing options...
Vaughan Posted April 2, 2011 Share Posted April 2, 2011 Check that the key values in the join table are correct. Link to comment Share on other sites More sharing options...
skingjack2 Posted April 2, 2011 Author Share Posted April 2, 2011 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 More sharing options...
comment Posted April 2, 2011 Share Posted April 2, 2011 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 More sharing options...
skingjack2 Posted April 3, 2011 Author Share Posted April 3, 2011 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 More sharing options...
comment Posted April 3, 2011 Share Posted April 3, 2011 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 More sharing options...
skingjack2 Posted April 4, 2011 Author Share Posted April 4, 2011 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 More sharing options...
Vaughan Posted April 5, 2011 Share Posted April 5, 2011 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 More sharing options...
comment Posted April 5, 2011 Share Posted April 5, 2011 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 More sharing options...
Recommended Posts
This topic is 4767 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 accountSign in
Already have an account? Sign in here.
Sign In Now