CODEWITHSUNDEEP
ruby-on-railsdatabasemodelassociations
Jens Schmidt
Jens Schmidt

Reputation: 79

Database modeling best practice: Dealing with Associations to the same "base"

I tried to normalize my database model. But I'm clueless how to do it in this case. Giving the following model:

I want to display all Systems of a Customer. That would be: @customer.systemobjects.each. That is already working.

Then I could add a System to a Cluster (which I mentioned is a "HABTM" association). In my Customer view I want to show only systems, that are not related to a cluster (also working with Cluster.includes(:systems).where(systems: { id: sysid }).present?).

Now my question: I want to display all Clusters (and Systems of that Cluster) of a specific customer, too. But, right now, I only to have the connection to customer through systems. For me, it would be easier, to add a reference to customer also in the cluster object (even though I would have this information already in the system).

Should I add this reference? Does it have something to do with normalization anyway? How would you assess this situation from a best practice point of view for a databases in general and for Ruby On Rails in specific? How would also be the best way, to go through each cluster of a customer, when I have it only through systems (how I could do it in rails?)?

Upvotes: 1

Views: 55

Answers (1)

rdnewman
rdnewman

Reputation: 1399

I think you'd prefer something like this:

class Customer
  has_many :systems
  has_many :clusters, through :systems  # expect `clusters_id` in System, which is typical
  # ...other code
end

class System
  belongs_to :customer
  belongs_to :cluster
  # ...other code
end

class Cluster
  has_many :systems
  has_many :customers, through :systems  # expect `customer_id` in System, which is typical
  # ...other code
end

This results in three tables, as your model already implies, but uses the systems table as a "hinge" lookup table for the other two without implication that clusters belong to systems (which doesn't make sense IRL as I understand your problem statement).

I often find has_many :through is often an easier and better choice than HABTM unless you truly have a mutual belonging relationship. You can go to the Rails Guides here and here (guide: "Active Record Associations") for more information on use :through. Definitely worth getting to know that guide for the kind of questions you have (though to be fair, it can take a bit of experience to fully appreciate the various options and how they're helpful).

Now when you want to refer to clusters that a customer has systems within, you merely need to write something like this:

  my_customer = Customer.find(some_id)
  customer_clusters = customer.clusters
  customer_systems = customer.systems

To find all the customers for a cluster (and so through clusters' systems), you'd write something like this:

  target_cluster = Cluster.find(some_id)
  cluster_customers = target_cluster.customers

If you want a want to produce a hierarchy of those (say, systems of a customer grouped under the clusters they belong to), it'd be something like this:

  my_customer = Customer.find(some_id)
  customers_systems = customer.systems.includes(:cluster)  # this brings in the associated cluster records without additional database operations

then either iterate or use group_by from the resulting data in customer_systems depending on how you intend to display or return the data.

Upvotes: 2

Related Questions