CODEWITHSUNDEEP
ruby-on-railspostgresql
ramamoorthy_villi
ramamoorthy_villi

Reputation: 2055

Best way to manage multiple category in database design

Objective: To design a best database table for a Trip

Table 01 : Trip (Columns: id, transport_mode_id....etc)

Table 02: Trains (id, name, number....etc.)

Table 03: Buses(id, name, number....etc)

I want to design a Trip table (transport_mode_id) with either train_id or bus_id. Trip should have either bus_id or train_id.

Should I create a two columns train_id, bus_id ? in the Trip table. Please suggest to design a trip table

Upvotes: 0

Views: 1279

Answers (1)

MZaragoza
MZaragoza

Reputation: 10111

What you are looking for is a polymorphic-associations Sometimes trip links to a bus, other times to a train, and planes, and boats.

A slightly more advanced twist on associations is the polymorphic association. With polymorphic associations, a model can belong to more than one other model, on a single association. For example, you might have a trip model that belongs to either a train model or a bus model. Here's how this could be declared:

class Trip < ApplicationRecord
  belongs_to : transportable, polymorphic: true
end

class Bus < ApplicationRecord
  has_many :trips, as: :transportable
end

class Train < ApplicationRecord
  has_many :trips, as: :transportable
end

on the Database 

class CreateTrips < ActiveRecord::Migration[5.0]
  def change
    create_table :trips do |t|
      ...
      t.references :transportable, polymorphic: true, index: true
      ....
    end
  end
end

if the table all ready exists 

class CreateTrips < ActiveRecord::Migration[5.0]
  def change
    add_reference :trips, :transportable, polymorphic: true, index: true
  end
emd

I hope that this helps

Upvotes: 2

Related Questions