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 ->

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: