3

(sorry for any bad English)

Let's suppose I have models A, B, C. Each model have one address.

In the book "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" (chapter 7 - Polymorphic Associations) there is a recipe to avoid such associations through the use of a "common super table" (also called base table or ancestor table).

Polymorphically, it would be:

table addresses:
    id: integer
    parent_type:string  # 'A', 'B' or 'C'
    parent_id: integer

I know you can use intersection tables, but the following solution looks more polished:

Instead of polymorphically associating A, B, C with Address, the recipe suggests to create a super table (Addressing) that have only a id field (surrogate key or pseudo key). Then, the other tables references Addressing. That way, says the author, "you can rely on the enforcement of your database’s data integrity by foreign keys". So, it would be:

table addressing
    id: integer
table addresses
    id: integer
    addressing_id: integer  (foreign_key)
    zip: string
table a
    id: integer
    addressing_id: integer  (foreign_key)
    name: string
table b
    id: integer
    addressing_id: integer  (foreign_key)
    name: string
table c
    id: integer
    addressing_id: integer  (foreign_key)
    name: string

The SQL query would be like this:

SELECT * from a
  JOIN address USING addressing_id
  WHERE a.addressing_id = 1243

The QUESTION is: how to code such scenario in Rails ? I've tried in several ways without success.

Fernando Fabreti
  • 4,277
  • 3
  • 32
  • 33
  • You can have a look at the RoR guide about associations [1], maybe you could find a way to do this with the :through option, sorry but I can't try in this moment. [1]: http://guides.rubyonrails.org/association_basics.html#the-has_one-through-association – Aldo 'xoen' Giambelluca Mar 11 '12 at 12:52

1 Answers1

1

Do objects A, B and C each have a single address? Or many addresses? From your comment below it looks like each object has one address.

If every object has just one address you can simply put a foreign key in the A/B/C objects with the address ID. Let the House or Office objects have one address:

class House < ActiveRecord::Base
  belongs_to :address
end

class Office < ActiveRecord::Base
  belongs_to :address
end

Your offices and houses DB tables need to have a foreign key address_id. This way you can access an object's address with something like house.address or office.address.

If these objects could have many addresses, the solution depends on the A/B/C objects. If they are related you could use Single Table Inheritance - Rails supports this pattern well - but without more information it's difficult to say which is the best approach.

GMA
  • 5,816
  • 6
  • 51
  • 80
Aldo 'xoen' Giambelluca
  • 12,075
  • 7
  • 33
  • 39
  • 2nd. line of question: "Each model have one address. Could you post the code so I can test it ? Looks like your solution requires foreign_keys for A/B/C on table 'addresses' reflecting the has_one association, what would cause a lot of nil values since a address is associated with A or B or C exclusively – Fernando Fabreti Mar 10 '12 at 11:48
  • "Each model have one address" so you put a reference to that address in your A/B/C models, you don't have a lot of nil values since each A object has or hasn't an address. I'll update my answer to explain it better. – Aldo 'xoen' Giambelluca Mar 10 '12 at 12:46
  • Looks good! What about referential integrity ? Certainly belongs_to :address, :dependent=>:destroy for the cases where you destroy a house object. But, what about destroying a Address ? – Fernando Fabreti Mar 11 '12 at 00:43
  • @FernandoFabreti mmm, it's difficult associate the address to something without a reference, you could add after_destroy to the Address model and search for objects with address_id equal to the id of the destroyed address but you should search in the offices and houses table and it's not a solution so beautiful. – Aldo 'xoen' Giambelluca Mar 11 '12 at 12:42