Database Design Question: Ensuring Constraints for Team, Player, and Coach Relationship

36 views Asked by At

I have a question regarding the design of my database tables and associated constraints.

enter image description here

I have the following tables and constraints:

  1. Team can have multiple Players.
  2. Team can have multiple Coaches.
  3. Player can belong to only one Team.
  4. Player can have only one Coach.
  5. Coach can belong to only one Team.
  6. Coach can have only one Player.
  7. Player and Coach must belong to the same Team.
  8. Player and Coach can be created without a Team.

Given these constraints, I am wondering:

  1. Should I enforce Constraint 7 using a Check constraint or handle it in the application logic?

  2. 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.

0

There are 0 answers