Database Relationship Structure (has_one and has_many together)

Hey Guys,

So I have a bit of a headscratcher here. I have a rails app which needs two relationships to what is the same table.

Basically, I have a Profile model. On that model I want to have one business location and many delivery locations.

The locations table content is found or created which are selected in multiples from a dropdown, and then added to the profile, i.e

location = Location.find_or_create(name: "Dublin")
profile.delivery_locations << location
profile.save

location = Location.find_or_create(name: "Cork")
profile.business_location = location
profile.save

I know I need a through table here, but I cant for the life of me think of how to do this. Would I need to make the through table polymorphic? and then alias in the Profile model? Or maybe ive gone totally the wrong way here?

Here's the base of what I have anyway:

class Profile < ApplicationRecord
    has_one :business_location, through: :locales, class_name: "Location", -> { where(type: "business_location") }
    has_many :delivery_locations, through: :locales, class_name: "Location", -> { where(type: "delivery_location") }
end

class Locale < ApplicationRecord
    belongs_to :profile
    belongs_to :location
end

class Location < ApplicationRecord
    has_many :locales
    has_many :profiles, through: :locales
end

Thanks!

I think you are looking for "Self Joins".

In designing a data model, you will sometimes find a model that should have a relation to itself. For example, you may want to store all employees in a single database model, but be able to trace relationships such as between manager and subordinates.

http://guides.rubyonrails.org/association_basics.html#self-joins

I dont think that one fits here unfortunately,

Ive updated my question a little bit with more.

But in essence im trying to keep my locations table dry, and queries flexible across models. Profiles will have a single business location and profiles have delivery locations, but they wont really every cross each other, they just happen to have the same source of data, i.e locations

You can do one thing, make the location table as single table inheritance pattern. So you have business_location and delivary_location. We can query and set relationships separately. For example

class Profile < ApplicationRecord
    has_one :business_location
    has_many :delivery_locations
end

Polymorphic types don't fit the domain model you described.
And this might be much simpler than you think. It might just be a column on your Profile:

class Profile < ApplicationRecord
  belongs_to :business_location, class_name: "Location"
  has_many :locales
  has_many :delivery_locations, through: :locales, class_name: "Location"
end