0

I need to check if I have the correct conceptual database model. My requirements

  1. A club can play more competitions and there can be more teams in the competition
  2. 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)
  3. 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?)
  4. A team member can act in multiple roles at once

Thank you in advance for all the comments

enter image description here

0

I think your conceptual schema represents what you said, for the most part. I'm assuming Team and 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 Owner and Club (e.g. Club would store OwnerId). Then a foreign key constraint would be setup on the Club.OwnerId referencing 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 Coach table.

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 Member called IsCaptain. This also allows you to fulfill the multiple roles in your fourth requirement.

4
  • I made a few edits, uploading the image to the original post. The owner must be a member. A team member can be an owner or a player or a coach (all together) – Aaron7 Apr 5 at 11:02
  • I added a 1: 1 configuration between the club and the owner. I start with databases, so I'm not sure if I'm the foreign key, the implementation is correct. I would like to ask if it doesn't matter that I have an ID with a team member and then again with the owner and the coach? I use identification dependent member-> owner, so that probably shouldn't be a problem right? The coach must have his own table, because other functions are linked to this – Aaron7 Apr 5 at 11:02
  • @Aaron7 just to clarify, since Owner and Coach need their own tables, the Club would have a foreign key reference for Owner and another foreign key reference for Coach to establish referential integrity. You may also need to define the Club.OwnerId and Club.CoachId as non-nullable columns, to ensure "exactly one" of each. It would not store any MemberId field though, the Member table itself would have a foreign key reference to Club on ClubId. The Coach and Owner will each have a MemberId so they also can have foreign key references to Member. – J.D. Apr 5 at 12:33
  • There should be no problems in doing any of that. – J.D. Apr 5 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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