CODEWITHSUNDEEP
ruby-on-railsrubydatabasepostgresqldata-structures
FO2495
FO2495

Reputation: 39

Having trouble figuring out how to best structure my database. Any direction or advice would be helpful

Relative newbie here. I am building an app that displays lineups from different soccer games. I have seeded the database through a remote API that has all of the data that I need for matches (which teams played, what was the score), players (the roster of each team), and teams (names, logos, schedule) and they're all associated appropriately.

Now, I'm trying to navigate building line-ups for each match. I need the app to recognize the players participating in each match as their own separate events.

I would like each match to have 2 line-ups (the line-ups all have a match_id that can be seeded from the API I'm using), and for those lineups to have all participating players (again, data that is available through the API).

Where I'm getting tripped up is should the lineup be its own separate thing that has_many players, or should it go through the team's matches? I'm new to structuring a database and this is my first real bump in the road so sorry if the answer feels self-explanatory.

Here are my models and schema file. Maybe I'm overthinking it.

class Team < ApplicationRecord
  has_many :players
  has_many :home_matches, :foreign_key => :home_team_id, :class_name => "Match"
  has_many :away_matches, :foreign_key => :away_team_id, :class_name => "Match"
  **has_many :lineups** 

  extend FriendlyId
  friendly_id :name, use: :history
end

class Lineup < ApplicationRecord
  belongs_to :team
  belongs_to :match
  has_many :players, :through => :lineup_players
end 

class Player < ApplicationRecord
    belongs_to :team
    has_many :ratings
    has_and_belongs_to_many :lineups, :through => :lineup_players
end

class LineupPlayer < ApplicationRecord
  has_many :players, :through => :teams
  belongs_to :lineup
end

class Match < ApplicationRecord
  require 'rest-client'
  belongs_to :home_team, :class_name => 'Team'
  belongs_to :away_team, :class_name => 'Team'
  **has_many :lineups**
end

Here is what my schema currently looks like...

  create_table "lineups", force: :cascade do |t|
    t.integer "match_id"
    t.integer "player_id"
    t.integer "team_id"
  end

  create_table "lineup_players", force: :cascade do |t|
    t.string "lineup_id"
    t.text "player_id"
  end

  create_table "matches", force: :cascade do |t|
    t.datetime "datetime", precision: nil
    t.integer "home_team_score"
    t.integer "away_team_score"
    t.integer "home_team_id"
    t.integer "away_team_id"
  end

  create_table "players", force: :cascade do |t|
    t.string "name"
    t.integer "team_id"
    t.string "slug"
    t.index ["slug"], name: "index_players_on_slug", unique: true
  end

  create_table "teams", force: :cascade do |t|
    t.string "name"
    t.text "logo"
    t.string "slug"
    t.index ["slug"], name: "index_teams_on_slug", unique: true
  end

Upvotes: 0

Views: 50

Answers (1)

keoghpe
keoghpe

Reputation: 404

Sounds like you need a join table or a has and belongs to many relationship between players and line ups here.

That will allow line ups to have many players and players to belong to many line ups. Your schema would then be something like:

  create_table "lineups", force: :cascade do |t|
    t.integer "match_id"
    t.integer "team_id"
  end

  create_table "lineup_players", force: :cascade do |t|
    t.integer "lineup_id"
    t.integer "player_id"
  end

Upvotes: 1

Related Questions