I have a question regarding the design of my database tables and associated constraints.
I have the following tables and constraints:
- Team can have multiple Players.
- Team can have multiple Coaches.
- Player can belong to only one Team.
- Player can have only one Coach.
- Coach can belong to only one Team.
- Coach can have only one Player.
- Player and Coach must belong to the same Team.
- Player and Coach can be created without a Team.
Given these constraints, I am wondering:
Should I enforce Constraint 7 using a Check constraint or handle it in the application logic?
Is the Player table violating BCNF since knowing a Coach reveals the Team, even though Constraint 8 allows Players and Coaches to exist without a Team?
I would appreciate any advice or insights on this. Thank you.
