I am building an app that have the following requirements:
-> A User can be a player of different teams. -> A Team can be of a sport type.
My question is:
-> Since for each sport type I want to store different information of a Player, what would be the best way to model that?
I have thought on having several models (and tables) for each kind of Sport, for example:
Basketball_Players, Football_Players and so on, but I am not sure if that would be a good approach. How do you usually do this on RoR?
I'd say you have two options, and I don't know that it's really possible to say which is the "most correct" way to do it without knowing the details of the requirements of your application.
What's a given is that you'll have a sport
table and a player
table. I can say that for sure. The question is how you connect the two.
Option 1: a single join table
You could have a table called player_sport
(or whatever) with a player_id
column, a sport_id
column, and a serialized_player_data
column or something like that, where you'd keep serialized player data (JSON, perhaps) depending on the sport. Pros: simple schema. Cons: not properly normalized, and therefore subject to inconsistencies.
Option 2: a separate join table for each sport
This is what you alluded to in your question, where you have a basketball_player
, football_player
, etc. Here you'd also have a player_id
column but probably not a sport_id
column because that would be redundant now that you're specifying the sport right in the table name. The need to have a serialized_player_data
column would go away, since you'd now be free to store the needed attributes directly in columns, e.g. wrestling_player.weight_class_id
or whatever. Pros: proper normalization. Cons: more complex schema, and therefore more work in your application code.
There's actually a third option as well:
Option 3: a combination of 1 and 2
Here you might do everything you would do in Option 2, except you'd move the common player attributes to the player_sport
table and save basketball_player
, etc. for the sport-specific attributes. So weight_class_id
would stay in wrestling_player
but player_sport
would have height
, weight
, and other columns that are relevant to all sports.
If you're looking for a recommendation, I would probably do Option 2, or, if it looks like there's enough overlap for it to make sense, Option 3.