I have 3 tables Injury, Sport, Exercise. I'm trying to wrap my head around if I should create a relation between them. I want to be able to list Injuries in the Sports and Exercises tables; this might expand to more tables in the future, so I'm thinking using a Polymorphic relation. However, that would create a multiple Injuries for both tables, i.e. Injurable_Type would be Exercises or Sports each with there own Injurable_id; if my understanding is correct.

Example:

Injury_id : 1
name = acl tear
injurable_id = 1
type = Sport

Injury_id: 2
name = acl tear
id = 1
type = Exercise

Ideally(though this might be impossible)

Injury_id : 1
name = acl tear
injurable_id = 1 
injurable_type = Sport and Exercise

I could keep them separate and store Injury_ids into columns on both Exercises and Sports tables but that will lead to querying issues/headaches down the line I believe. Further more, my understanding of polymorphic associations is to get rid of the Injury_id column.

How should I approach this problem? Leave the tables separate and deal with the possibility of multiple queries down the road or make them polymorphic or something else?

My thought process is this:

Sport / Exercise table would have access to all injuries in Injury table

What I have though of so far:

Injury
belongs_to :injurable, polymorphic: true

Sport
has_many :injuries, as: :injurable

Exercise
has_many :injuries, as: :injurable

Or just leave them separate
Injuries_id: 1, 2 ,3 etc.

Sports table has column
injuries_id: 1, 2

Exercises table has column
injuries_id: 1, 3

Then query them (polymorphic) like:

Sports controller:

sport = Sport.includes(:injuries).find(1)

Exercises controller

exercise = Exercise.includes(injuries).find(1)

Then query them (Separate way) like:

Sports controller:
    sport = Sports.find(1)
    injuries = sport.pluck(:injuries_id)
    injury_name = Injuries.find(injuries).pluck(:name)

Exercises controller:
    exercise = Exercises.find(1)
    injuries = exercise.pluck(:injuries_id)
    injury_name = Injuries.find(injuries).pluck(:name)

Something like that.

CodePudding user response：

My concern with the polymorphic approach is that you won't be able to share the same injury "definition" across different activities. You'll have to duplicate the same injury to attach it to other sports and exercises; this may cause more troubles down the line(and goes against database normalization rules). For example, answering a question such as "how many total ACL tear injuries across all activities?" would not be quickly done(you'll end up matching by text -- that's not ideal!). Also, what if you decided to change the name of the ACL tear injury? You'll end up updating multiple rows in the Injury table.

I would suggest a third approach(similar to your 2nd approach): Injuries should not be directly linked to sports or exercises. The Injury model should do one thDefinehould do it well: Defining what an injury is. This will serve you better as you expand your data schema and add new use cases. Now, associating a sport or exercise activity can be done with a bridge model, i.e.:

class SportInjury
  belongs_to :injury
  belongs_to :sport
end

class ExerciseInjury
  belongs_to :injury
  belongs_to :exercise

Then, you're able to associate a Sport model to an Injury model as the following:

class Sport
  has_many :injuries, through: :sport_injuries
end

Ruby on Rails should be smart enough to produce optimized SQL when accessing a sport's list of injuries when you call sport.injuries.

This approach has a couple of benefits:

• Injuries are not duplicated, and updates to them are done once.
• Linking new activities to injuries is straightforward.
• You can attach additional data in the bridge entities(e.g., time of injury during a sports match can be stored as an attribute in SportInjury)
• Finally, in comparison to your 2nd approach, this is the "RDMS Way." Generally in an RDMS, you encode associations in tables rather than as array columns(which is a practice you may see in a document-based DB such as MongoDB).