Optimal design for addresses and entities (customers, suppliers, pickup points)

Question

I know there are many questions about this pattern but I didn't find the perfect solution. I had a close look to those SO pages : 32996898, 24039964, 648463, 15503913, 2494452

In my app, several models have one or more (it depends) addresses. I try to figure how to design it properly. I found two solutions but not ideal in my opinion.

Option#1 : a.k.a the normalized one :

addresses              (id, alias, line_1, line_2, city_id, country_id)  
address_types          (id, name) {name: billing, shipping, ...}
customer_addresses     (id, address_id, address_type_id, default, status)  
supplier_addresses     (id, address_id, address_type_id, default, status)  
pickup_point_addresses (id, address_id, address_type_id, default, status)

I like the addresses and address_types tables but the three last are very similar.

Option#2 : a.k.a the polymorphismed one :

addresses             (id, address_type_id, addressable_type, addressable_id, alias, line_1, line_2, city_id, country_id, default, status)
address_types v       (id, name) {name: billing, shipping, ...}

Good, but I don't like the fact that I don't have a simple address in addresses. I would like to keep the Address' schema of Option#1. Also, many user's can share a same address (especially for a pickup point)

Option#3 : a.k.a the 'something between I would to propose you' one (also polymorphic):

addresses             (id, line_1, line_2, city_id, country_id)
entity_addresses      (id, addressable_id, addressable_type, address_id, default, status, alias)

class Address < ActiveRecord::Base
  belongs_to  :city
  belongs_to  :country
  has_one :entity_address
  ...
end

class EntityAddress < ActiveRecord::Base
  belongs_to :addressable, :polymorphic => true
  belongs_to  :address
  ...
end

class Customer < ActiveRecord::Base
  has_many :addresses, as: :addressable
  ...
end

class Supplier < ActiveRecord::Base
  has_many :addresses, as: :addressable
  ...
end

class PickupPoint < ActiveRecord::Base
  has_many :addresses, as: :addressable
  ...
end

class Order < ActiveRecord::Base
  belongs_to :customer
  belongs_to :shipping_address, class_name: 'Address'
  belongs_to :billing_address, class_name: 'Address'
  ...
end

Con(s) :
- entity_address is such a horrible name for a table. (please help me find a new name, I found Addressable also bad for a name table)

Pro(s) :
- address table is aptly named and serves only one purpose. no duplicates in this model. Every address will be unique. - addresses will be immutable (only create for the integrity of data - shipping address in orders, for example)
- entity_address allows to manage addresses (changing alias, make an address the default one, deleting, etc ...)
- No more address_type table. In orders table I will add two foreign keys (shipping_address, billing_address)

What are your thoughts about the option#3 ? I am also not sure about the last point (two foreign keys in the orders table).

Optimal design for addresses and entities (customers, suppliers, pickup points)

0 Answers0