All we need is an easy explanation of the problem, so here it is.
I need to check if I have the correct conceptual database model. My requirements
- A club can play more competitions and there can be more teams in the competition
- The owner, player or coach may not work in more than one team (I assume that this is an integrity limitation and is not included in the conceptual model)
- The club must have exactly one owner and exactly one coach (I’m not sure how to implement it here, is it also an integrity restriction?)
- A team member can act in multiple roles at once
Thank you in advance for all the comments
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
I think your conceptual schema represents what you said, for the most part. I’m assuming
Club are synonymous here. If so, does a
Member of the
Club need to be the
Owner or can it be a non-member?
In any case, you’ll need to setup a one-to-one relationship between
Club would store
OwnerId). Then a foreign key constraint would be setup on the
Owner.OwnerId to ensure the referential integrity of your second point.
Typically the foreign key constraint will also ensure that the
Club.OwnerId is non-null, i.e. each
Club has one
Owner as part of your third requirement. You’ll also want to follow the same foreign key constraint on
Club.CoachId which will either reference a separate
Coach table or the
MemberId of the
Members table, if you don’t have a
If you don’t have a separate
Coach table then you’ll need a
IsCoach boolean based field in your
Member table, and you should do the same for the
Captain entity. Instead of a separate table, you can easily store a boolean based field on
IsCaptain. This also allows you to fulfill the multiple roles in your fourth requirement.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂