Active Record design seems awkward and possibly brok

I'm not suggesting that the OP fudge their schema at all. I'm suggesting
that it was intrinsically flawed to start with, and that the lack of an
AR way to go about it might be an indication of the problem.

The original poster is right on. Perhaps the example doesn't make it
clear, so let me present another example.

I have a table called "cities", which is pretty much a static lookup
table. You'll find attributes such as: lat, long, population, name, ...

I have a table called "users" for each user. When a user registers, he
specifies his "city". Essentially a User has-one City.

So now if i add a FK in cities to point back to users, with the
corresponding belongs_to in the city model, rails is happy.

But in my app users go on adventures. When they do, their *current* city
gets copied over into the adventure, so that the adventure remains
static over time (w/respect to city). Eg, a user sets his city to
Chicago on wednesday, on thursday he participates in adventure A1, so
therefore A1's city is "Chicago". On Friday the user changes his
location to Rochester, and then participates in adventure A2. A2's city
is now "Rochester".

So i also have the relationship: Adventure has-one City. But how do i do
this? Do i add a column to Cities called "adventure_id" and the
corresponding belongs_to? That's just ugly. I don't want to keep adding
parent id columns to the city table just because some other entity wants
a relationship with a city.

Do i denormalize and include all of the city attributes in each of User
and Adventure? No way, that's also nasty; consider a user participating
in many adventures over time.

What i really want is to add a column "city_id" to both the User table
and the Adventure table; and whatever other table might be intersted in
having a relationship with City.

In the abstract, this is the notion of a lookup table, where many
entities might have an attribute that is the FK to some specific lookup
table. The FK is in the parent table pointing to the lookup table. This
is a perfectly reasonable relational design.

It's as if AR is missing some sort of "has_reference"; you would only
put this in the parent table. You wouldn't need any sort of "belongs_to"
declaration in the City table, since you wouldn't traverse the model
objects in that direction.

class User
  has_reference :city
end

class Adventure
  has_reference :city
end

class City
#nothing needed
end

"has_reference :city" == "belongs_to :city"

belongs_to :city means you have a foreign key referencing a city, like
users.city_id -> cities.id.

Then, when you create a new adventure, default its starting city to
the user's current city.

Best,
jeremy

Did I miss it or has nobody considered using a polymorphic association
for this case?

class School < ActiveRecord::Base
  has_one :address, :as => :addressable
end

class Student < ActiveRecord::Base
  has_one :address, :as => :addressable
end

Jeremy Kemper wrote: