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.