Recursive Association with compound primary key in SQL database

15 pts.
Tags:
Database issues
Design principles
SQL
SQL Database
SQL Server database
SQL Server errors
Hello, I'm afraid I'm very new to this and really struggling. I have a table with a compound primary key composing of Code, Date, Client to represent an attendee on a course. I need to include a 'Lead Client' column to represent a recursive relationship where one attendee can be nominated to lead others attending the same course – i.e. can lead other attendees with the same Code and Date only. The Code and Date need to be the same for the Lead Client as for the Client. At the moment I have set up a composite Foreign Key in the table which references the Primary Key of the same table and comprises of 3 columns: Lead_Code, Lead_Date and Lead_Client. I have set up the FK so that either all or none are null, and have set up the Lead_Code and Lead_Date so that their values must match the Code and Date of the same record. However - this does not seem to be a very tidy or efficient way of storing the data, as each row where a Lead_Client is listed will basically contain two columns containing the same Code, and two containing the same Date. It also means that anyone inserting data into the table will need to enter the Code and Date twice wherever a Lead_Client is listed. I can't help feeling that there must be a simpler solution but I can't work it out. Ideally I would like to simply include a 'Lead Client' column, but somehow force the table to only accept values where Lead_Client belongs to a record as the Client (as part of the PK) where the Code and Date match the Code and Date of the original Attendee record. Example: Code CRS1 Date 01/10 Client AA Lead_Client Null Code CRS1 Date 01/10 Client BB Lead_Client AA Code CRS2 02/10 Client CC Lead_Client AA = NOT ALLOWED as Client AA is not an attendee on course Code CRS2 on Date 02/10 Apologies if this doesn’t make sense – I’d be really grateful for any help on this as I’m really stuck. Thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

I see three options for you.

1. Change the foreign key so that it uses the same columns for the parent and the child.

2. Add computed columns to the table so that if the Lead_Client value is not null then the values are entered in automatically.

3. Change the front end so that the only people who show up in the Lead_Client drop down list are the ones who have the same Code and Date.

Option #3 would be the best option as this will prevent the users from selecting incorrect leads (which they will if you let them).

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Littlegreen
    Thank you so much!! I have implemented option 2 for the meantime and it's working fine - might have a go at option 3 later. Really appreciate your help
    15 pointsBadges:
    report
  • Littlegreen
    [...] 1. Mrdenny provided three clear options for a member struggling with recursive association with compound primary keys in SQL database. [...]
    0 pointsBadges:
    report
  • Littlegreen
    [...] 5. Mortimer1 cast his vote for the best solution for deploying Linux virtual desktop. 6. Mrdenny laid out three great options for a member asking about recursive association with compound primary key in SQL database. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following