My question MIGHT be "ActiveRecord play nice with multiple views into a
table?
I ask because my application has a single DateDimension table (heavily
ornamented date objects). Each SalesFact has TWO foreign keys into the
DateDimension table: one for the date a house went on the market, one
for when it was sold [*].
My first model+schema is shown below, but I'm stymied how to construct a
query that joins the SalesFact table with the Dimension tables. More
specifically, how do you craft a :select that includes on_market_date
and a sale_date, since they're both foreign keys into the same
date_dimension table? Extra points if you can do it all with
associations and not drop down into SQL.
If that's not possible, I'm pretty sure I can create multiple views of
DateDimension to eliminate the ambiguity. But if there's a more RoR'ish
way to do this, I'm all ears.
# ===== the models...
class SalesFact < ActiveRecord::Base
belongs_to :address_dimension
belongs_to :on_market_date_dimension
belongs_to :sale_date_dimension
end
class AddressDimension < ActiveRecord::Base
has_many :sales_facts
end
class DateDimension < ActiveRecord::Base
end
class OnMarketDateDimension < DateDimension
has_many :sales_facts
end
class SaleDateDimension < DateDimension
has_many :sales_facts
end
# ===== and the schema...
create_table "sales_facts", :id => false, :force => true do |t|
t.integer "address_dimension_id"
t.integer "on_market_date_dimension_id"
t.integer "sale_date_dimension_id"
t.float "asking_price"
t.float "sale_price"
end
create_table "address_dimensions", :force => true do |t|
t.string "house_number"
t.string "street_name"
... lots and lots of other fields
t.float "latitude"
t.float "longitude"
t.string "postal_code"
end
create_table "date_dimension", :force => true do |t|
t.datetime "datetime"
t.boolean "is_weekend"
t.boolean "is_holiday"
... lots and lots of other fields
t.string "day_name"
t.string "month_name"
t.integer "quarter"
end
# =====
[*] NOTE: Many people say "why don't you just put the two dates directly
into the SalesFact table?" Rather than tilt at that windmill, I'll
simply offer two pointers on the topic:
http://philip.greenspun.com/sql/data-warehousing.html (excellent
intro)
http://www.kimballgroup.com/html/booksDWT2.html (from the man
himself)
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
