FK in the same table ?

[bachgen]

Hello,

I have a table which holds information about people (name, address, dob, joined, mentor). The problem I am having is that the mentor fields will be another person in the people table, I am not sure how to achieve this.

Can you help.

Example
James, Mary, Luke are all in the people table, Luke is James mentor and so mentor is a fk how to I link this up to the same table. ??

 

[Rabbie]

You need to use a self join. See this link for more info

http://allenbrowne.com/ser-06.html

Basically you would put Lukes PK in the Mentor field of James

 

[bachgen]

Rabbie, thanks for your reply.

I have been thinking and this method would cause an issue if that person was deleted from the system ?!?

maybe it would be better if it was just text then.

Does anyone know how is this handled in other db's ?

Bachgen.

 

[Banana]

I don't think this will be an issue. Rabbie is spot on about self-join.

If you delete a person from the record, the mentor's record still will be there, the mentor just no longer has its ID showing up for that deleted record.

OTOH, if you delete a mentor from record and the relationship integrity is enforced, Access will return an error telling you to delete all related records before deleting mentor's record. Checking "Cascade delete" will allow Access to go and delete the mentor record *AND* all the related records.

That said, are you sure you do need to delete records? I've never seen a good reason to do so; if I need to remove records from the database because they no longer apply to the present business needs, I simply archive them.

 

[bachgen]

Banana,
Thanks for your reply.

They want to ensure the data behind the person is deleted when they leave. I am doing this for a local charity and they want to make sure we are not keep data when volunteers have left.

Even after a volunteer who was a mentor has left I want to be able to say which volunteer had which mentor, I just won't like the mentor to the volunteer.

make sence ??

 

[Simon_MT]

I would put your Mentor into a CombiBox and then use DLookup back on the Table to get their name, if necessary. If the Mentor gets deleted it doesn't matter.

Simon