Best method of many to many database?
I am currently working on an app for sporting clubs, this allows club admins to manage the divisions, teams, and ultimately the players.
At present I have my database/relationships as follows
class Sport < ActiveRecord::Base has_many :clubs has_many :teams attr_accessible :club_id class Club < ActiveRecord::Base belongs_to :sport has_many :teams has_many :users has_many :divisions attr_accessible :sport_id class Division < ActiveRecord::Base has_many :teams belongs_to :club attr_accessible :club_id class Team < ActiveRecord::Base has_many :users has_many :schedules belongs_to :division belongs_to :club attr_accessible :division_id, :club_id class User < ActiveRecord::Base belongs_to :club belongs_to :team attr_accessiable :club_id, :sport_id
essential what I would like is a more concise way of managing this. i.e.
- User can only belong to 1 club, within 1 division, but can have multiple teams
- Team can only belong to 1 division, within 1 club, within 1 sport, but have multiple users
- Division can only belong to 1 club, within 1 sport, but have multiple teams
- Club can only belong to 1 sport, but have multiple teams, and multiple divisions
At present the above is working, but i dont think the relationships/structure is at its best
You only have singular sport clubs in your country? Here in Germany different divisions of a club can have different sports (my own club e.g. is in Athletics, Cycling, Volleyball, Basketball, Tabletennis and Swimming). In most of my apps I just skip the sport and the divisions: The sport is given from the app's context, and I view club and division as identical. My Athletic-apps just deal with the club, not with the club's division. I view this sacrifice as necessary, as the complexity otherwise overwhelms me – your mileage may vary, of course :-)
Moreover: A user only temporarily belongs to a club, so I have an intermediate relationsship between users and the teams; "Startberechtigung" in german, something like "license" in english I 'ld say; it belongs_to team and belongs_to user and has "start_date" and "stop_date" as additional attributes. That way I even have histories for clubs and users. Important for record lists (again: Athletics)!
After that I would throw away the superfluous transitive relationsships: A user belongs to a team which in turn belongs to the club => use has_many :through's more; you dont need the user belongs_to club.
Now your models should be a bit cleaner.
Dont use classname "User" for the players, this will ultimately lead to a conflict with your user/admins-model. "Player" or "Athlete" is just fine for me.
You dont need the :through in each and every case, of course. A scope with includes or a class method might be preferable for you, esp. if you want to bridge over more than one intermediate class.
How about something like (please fill the holes from the recommended guide):
class Sport < ActiveRecord::Base has_many :clubs has_many :teams, :through => :clubs, :readonly => false class Club < ActiveRecord::Base belongs_to :sport has_many :teams class Team < ActiveRecord::Base belongs_to :club has_many :athlete_teams has_many :athletes, :through => :athlete_teams, :readonly => false class AthleteTeam < ActiveRecord::Base belongs_to :teams belongs_to :athletes class Athlet < ActiveRecord::Base has_many :athlete_teams has_many :athletes, :through :athlete_teams, :readonly => false