1
$\begingroup$

I am fairly new to ER modelling, and I am finding myself a bit confused on the best practice for the following toy scenario;

Say I have several local tennis clubs, each of which has at most one teams from each 'grade' i.e. an A grade team, B grade team etc., and I am interested in storing data for each team's training sessions.

If we assume a team cannot train more than once per day, what would be the best approach to this ER model? My first thought is to use a chain of weak entities which all stem from the strong club entity, i.e. Clubs are strong entities (presumably with the primary key of the club location?) Teams are weak entities belonging to clubs (with a partial key of the grade the team plays in) Training sessions are weak entities belonging to teams (with a partial key of the date the training session takes place on).

However, I was wondering if instead, a ternary relationship should/could be used. I have not really used ternary relationships in the er model questions i have approached so far, nor do I fully understand when they are the optimal choice, so any information on the toss up between using ternary relationships vs. a chain of weak entities (in the context of my toy example if possible, or in general if it is easier to do so) would be greatly appreciated. Thank you very much for reading this, have a lovely day :]

$\endgroup$

1 Answer 1

0
$\begingroup$

What entities would the ternary relationship connect here? Club, Team and Session?

Club and Team are redundant since Team and Session are weak. Knowing which particular Session is being considered we also know unequivocally what Team and Club must be. So the chain of weak entities is the way to model this.

However, let's think about the fee to participate. In general club Y will have different rates to club Z. Grade A classes will be more expensive than grade B. Evening lessons will differ from morning lessons. Now we have three entities - Club, Grade and Timeslot - which are independent. To know the particular we would need the identifiers for all three entities.

That's the logical point of view. The physical implementation is easier because both options amount to the same thing. Because Team and Session are weak the key(s) of the parent entity are carried into the children. So we get tables like this.

Club
  club_location
  primary key: club_location

Team
  club_location,
  grade,
  primary key: club_location, grade

Session
  club_location,
  grade,
  session_date
  primary key: club_location, grade, session_date

You can see the implementation of Session is identical to how it would have been had it been modeled as a ternary relationship.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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