DB Relationship Consideration

[VannSystems]

Hi,

I need some help planning a proper DB relationship between 4 models. The models are Company, Customer, Vendor, and Contact. A Company is either a Customer or a Vendor. Each Company has many Contacts. Customer's have certain relationships that Vendors do not.

The Customer and Vendor part is what is tripping me up. I know to make the Company and Contacts a one to many relationship.

I hope I am explaining this well enough. Let me know if you need more information. Your help is greatly appreciated!

-Matt

[Fenrir2]

A customer is a company? Are you sure?

Can't you just use three models: Customer, Vendor and Contact, or is this impossible?

[VannSystems]

This might be possible. Can I still have a list of all contacts whether they are customer or vendor?

[ReggieB]

It appears to me that being a customer or a vendor is a property of company. Therefore, you could just have a field in the company table called something like 'relationship' with possible entries of 'customer' and 'vendor'. Then to get all customers, you'd simply use:

Company.find_all_by_relationship('customer')

If it is that simple, then job done, short of adding some validation tasks and control to ensure only the correct entries of 'customer' or 'vendor' appear in the relationship field.

The problems arise if you have a company that is both a customer and a vendor. Also what do you do if you need to add a new relationship: for example 'partner' or 'competitor'.

Therefore, if the simple solution above won't work for you, I think the best scalable option would be to add a relationships table to store and define the types of relationship. You could then use has_to_and_belongs_to_many to join the tables via a companies_relationships join table. You could then find customers via:

Relationship.find_by_name("customer").companies

[Fenrir2]

ReggieB, your first solution is OK, but Rails already has this kind of thing built-in with STI (single-table-inheritance). STI is solution 1.

Solution 2 uses polymorphic associations:

class Contact < ActiveRecord::Base
  belongs_to :contactor, :polymorphic => true
end
 
class Customer < ActiveRecord::Base
  has_many :contacts, :as => :contactor
end
 
class Vendor < ActiveRecord::base
  has_many :contacts, :as => :contactor
end

So which one to choose. If a customer *is* a company, and if the differences between a customer and a vendor are minor, you'd pick solution 1. If a customer isn't a company, or if there are big differences between a customer and a vendor, you'd pick solution 2.

Single table inheritance:
http://wiki.rubyonrails.org/rails/pa...bleInheritance

Polymorphic associations:
http://wiki.rubyonrails.org/rails/pa...icAssociations
http://wiki.rubyonrails.org/rails/pa...icAssociations

[VannSystems]

Thanks for your responses! Great ideas. Now that I think of it, we might be storing companies as competitors or partners as well. Even if we deal with just one individual they are either working for a company or they own a company. It sounds like Polymorphic associations will be the way to go for scalability. Ideally, we want to be able to filter results in any way that will help us find the information that we need quickest.

Do you agree?

[ReggieB]

Fenrir2,

Yes I agree. Polymorphic association is a very neat solution to the problem.