One To Many Relationship & Referential Integrity

[dom_donald]

OK, I don't know if i'm being completely stupid here, but here goes anyway :

I have a table containing a list of 'Tasks'. For each Tasks, you can specify between 1 and 4 'staff members' who are working on it. Therefore I have 4 fields in the Tasks Table, Staff_1, Staff_2, Staff_3 and Staff 4. Each of those fields holds an index into the 'Staff Table'.

In the Database Relationships, I want to maintain referential integrity such that you can not delete a member of staff from the 'Staff Table' if they are being used on a Task (in any of the four slots). I therefore specified a relationship, one-to-many, between each of these four staff slots and the Staff Table index field.

Trouble is, it's a One To Many relationship and not a Zero To Many. This means I can now no longer add a Task to the Task Table unless I specify a 'Staff member' for EVERY slot (Staff_1, Staff_2, Staff_3 and Staff 4).

PROBLEM: I would like to have BETWEEN 1 and 4 staff members i.e. I would like to be able to specify one staff member for a Task and leave the others blank - but "" is not a valid Staff Member in the Staff Table so the referential integrity is violated.

How do I get round this, without having a Staff Member called ""?

 

[Rich]

You do not need four separate fields to enter staff numbers, just one field which identifies the staff number selected for the task

 

[dom_donald]

You do not need four separate fields to enter staff numbers, just one field which identifies the staff number selected for the task

Rich - I need 4 fields because I wish to assign between 1 and 4 different staff members to a job. Trouble is, I may only assign one staff member, with the others being blank.

The difficulty comes when trying to append a record to the 'Tasks Table' when some of the staff member fields are blank - the referential integrity rules insist that the staff names selected for each of the FOUR slots exist in the 'Staff Table'. Unfortunately a 'blank' name doesn't exist, so the record can not be appended. Hope this helps to clarify!

 

[Rich]

You don't need four fields, you can have between 1 and four records for each event, so you should have a One to Many relationship between Tasks and Members. Search here for articles on normalisation.

 

[dom_donald]

erm.. ok, I think i got what you're saying although it's not very clear to me.

Are you saying that I should introduce another table to hold the information about the staff who are assigned to Tasks, indexed by the Task Id? (see attached).

But this way, I don't have any fields in the Tasks table relating to the Staff who are doing the task..