conceptual schema

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

  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

conceptual schema

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.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply