SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Location
    Texas
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question DB Relationship Consideration

    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
    Web design is cool. Roll with it.

  2. #2
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A customer is a company? Are you sure?

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

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Location
    Texas
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This might be possible. Can I still have a list of all contacts whether they are customer or vendor?
    Web design is cool. Roll with it.

  4. #4
    SitePoint Evangelist
    Join Date
    Feb 2006
    Location
    Worcs. UK
    Posts
    404
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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:
    Code:
    Relationship.find_by_name("customer").companies

  5. #5
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code ruby:
    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

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2007
    Location
    Texas
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Web design is cool. Roll with it.

  7. #7
    SitePoint Evangelist
    Join Date
    Feb 2006
    Location
    Worcs. UK
    Posts
    404
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fenrir2,

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •