0

I'm working on a school project and I need some help.

I want to create three tables which should contain these fields:

User UserID (AI, PK), UserEmail, UserPassword

WorkGroupMember GroupID(PK, FK WorkGroup) UserID (PK, FK User)

WorkGroup GroupID (AI, PK), GroupFacilitator, GroupName

enter image description here

The question is, GroupFacilitator in WorkGroup is actually UserID, can or should it be a foreign key?

Thank You

closed as off-topic by RolandoMySQLDBA, Max Vernon, Kin Shah, Philᵀᴹ, gbn Apr 30 '14 at 7:08

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – RolandoMySQLDBA, Max Vernon, Kin Shah, Philᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

2

First you should clarify the relationships between your entities by asking these questions:

  • Can a facilitator of a group be a user that is not a member of that group?
    Or a facilitator must always be a member of that group?

  • Do groups always have a facilitator? (your design indicates: yes)

  • Does every group have one facilitator only? (your design indicates: yes)
    Or some groups may have more than one?

If the answer to the first question is "Yes", then the foreign key constraint should be referencing User (and you have no circular path, only multiple paths in your design.)

If the answer is "No", then the foreign key constraint should be referencing WorkGroupMember, with a composite foreign key (and then you have a circular path):

FOREIGN KEY (GroupID, GroupFacilitatorID)
  REFERENCES WorkGroupMember (GroupID, UserID)

The circular design makes it difficult to be implemented in many DBMS (because the constaints need to be DEFERRABLE and that is not implemented in many of them.)

Another solution would be (as proposed by Steve Pettifer in his answer), to add a IsGroupFacilitator column in the WorkGroupMember and a partial (filtered) unique constraint, to enforce that each group have at most one facilitator:

UNIQUE (GroupID) 
  WHERE (IsGroupFacilitator = 1)

This way however, you cannot enforce that all groups have a facilitator (not with constraints at least, you can still enforce it by taking care of it in the INSERT/UPDATE/DELETE statements)

1

An alternative might be to have a field in WorkGroupMember called IsGroupFacilitator (or something else suitably descriptive of its function) which defaults to whatever you decide should mean no (perhaps 0 if it is a numeric type) but can be set to a non-zero value (1 is considered the traditional non-zero value) for those users who are facilitators. This makes more sense to me given that being a facilitator is a property of a WorkGroupMemeber. Referential integrity is very important but it is not always the right answer to a question and too much can make your DB overly complicated.

You spotted the possibility of circular dependency but this is not in itself going to create a problem: You're simply saying that whatever goes in the WorkGroupFacilitator field in WorkGroup must first exist in User and therefore you your order of table population would be User followed by WorkGroup and finally WorkGroupMember which is logical. However this would still be the enforced order even if you did not have an FK from User to WorkGroup.

0

Yes, it should be a Foreign Key. Creating of it will allow server to optimize the execution of queries joining Work Groups and treir Facilitators.

  • Should it be a foreign key to UserID in WorkGroupMember? Wouldn't that make a circular dependency? I added a picture, don't know if that helps. – Mangs Apr 3 '14 at 10:53
  • WorkGroupMember is actually a table implementing an n-to-n relationship, it is not quite entity in your sample. I don't think it makes sense to use it for Facilitator foreign key. But in case your WorkGroupMember table has some additional data (fields which you did not specified in the example) related to member, and facilitator IS ALWAYS (logically) a member of the group, it is better to use WorkGroupMemeber key as facilitator. Hope this helps. – levitologista Apr 3 '14 at 11:02
  • Foreign key constraints are there to enforce integrity, not efficiency. They sometimes have a positive effect on efficiency but that's usually due to the added indexes (to enforce the foreign key constraints) or by helping optimizer to simplify a query. – ypercubeᵀᴹ Apr 3 '14 at 11:34
  • Are you sure it is true for any DBMS? I meant indexes which are created automatically with FK in most DBMS. – levitologista Apr 3 '14 at 11:36
  • 1
    Many do but not all. And anyway, a foreign key is a constraint, not an index. – ypercubeᵀᴹ Apr 3 '14 at 11:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.