# How to ensure two tables reference the same record in a third table?

**URL:** <https://www.ruby-forum.com/t/how-to-ensure-two-tables-reference-the-same-record-in-a-third-table/176174>\
**Category:** Rails\
**Created:** [September 25, 2009, 7:39pm UTC](https://www.ruby-forum.com/t/how-to-ensure-two-tables-reference-the-same-record-in-a-third-table/176174 "2009-09-25T19:39:43Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![oldbug](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c0e974/32.png) [@oldbug](https://www.ruby-forum.com/u/oldbug)\
**Post date:** [September 25, 2009, 7:39pm UTC](https://www.ruby-forum.com/t/how-to-ensure-two-tables-reference-the-same-record-in-a-third-table/176174/1 "2009-09-25T19:39:43Z")

</div>

class Part \< ActiveRecord::Base  
has\_many :lots  
has\_many :components # or, maybe not  
end

class Lot \< ActiveRecord::Base  
belongs\_to :part  
has\_many :components  
end

class Component \< ActiveRecord::Base  
belongs\_to :part # or, maybe not  
belongs\_to :lot  
end

In my domain, a “lot” of “components” has a part number (which I  
represent in RoR as a #belongs\_to relationship, even though the term  
“belongs\_to” is somewhat misleading). Each component in the lot has a  
part number which is the same as the part number for the entire lot.

What is the best way to ensure that this consistency is maintained?  
That is, that all parts in a lot reference the same part number as the  
lot itself.

One way to ensure the consistency is to remove the “part\_id” column  
from the component table and to reference a components part number  
through the lot association, i.e. mycomponent.lot.part.number. The  
problem is, sometimes I want to look at all of the parts that have a  
given part number, and the only way I can see to do that is to collect  
all of the lots with a given part number and then collect all of the  
components in each of the lots, using something like:  
mypart.lots.map(&:components).flatten. Unfortunately, that returns an  
array of components instead of the dynamic finder that would be  
returned by mypart.components.

A second way to ensure the consistency is to add validations to the  
Lot and/or Component models. I could do that, but I have been advised  
elsewhere that validations are best when augmented by database  
constraints. (See  
[http://groups.google.com/group/rubyonrails-talk/browse\_thread/thread/3b023e81df58619d](http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/3b023e81df58619d),  
if you are interested). Is there some database agnostic way to add  
this sort of constraint?

Or is there some other mechanism to implement this?

–wpd

---

<div class="post-metadata">

**Author:** ![oldbug](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c0e974/32.png) [@oldbug](https://www.ruby-forum.com/u/oldbug)\
**Post date:** [September 25, 2009, 7:55pm UTC](https://www.ruby-forum.com/t/how-to-ensure-two-tables-reference-the-same-record-in-a-third-table/176174/2 "2009-09-25T19:55:47Z")

</div>

Patrick D. wrote:

> class Part \< ActiveRecord::Base  
> has\_many :lots  
> has\_many :components # or, maybe not  
> end
> 
> class Lot \< ActiveRecord::Base  
> belongs\_to :part  
> has\_many :components  
> end
> 
> class Component \< ActiveRecord::Base  
> belongs\_to :part # or, maybe not  
> belongs\_to :lot  
> end
> 
> In my domain, a “lot” of “components” has a part number (which I  
> represent in RoR as a #belongs\_to relationship, even though the term  
> “belongs\_to” is somewhat misleading). Each component in the lot has a  
> part number which is the same as the part number for the entire lot

Then the part number is a property of the lot, not the component.

> What is the best way to ensure that this consistency is maintained?  
> That is, that all parts in a lot reference the same part number as the  
> lot itself.

Model the data correctly. Represent the part number once and only once,  
presumably in the lot.

> One way to ensure the consistency is to remove the “part\_id” column  
> from the component table and to reference a components part number  
> through the lot association, i.e. mycomponent.lot.part.number.

That is the correct way to do it. Repeating data in the database is  
always a sign that something is wrong with your schema.

> The  
> problem is, sometimes I want to look at all of the parts that have a  
> given part number, and the only way I can see to do that is to collect  
> all of the lots with a given part number and then collect all of the  
> components in each of the lots, using something like:  
> mypart.lots.map(&:components).flatten.

Part has\_many :components, :through =\> lots

mypart.components

Done!

[…]

> –wpd

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![oldbug](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c0e974/32.png) [@oldbug](https://www.ruby-forum.com/u/oldbug)\
**Post date:** [September 25, 2009, 8:00pm UTC](https://www.ruby-forum.com/t/how-to-ensure-two-tables-reference-the-same-record-in-a-third-table/176174/3 "2009-09-25T20:00:48Z")

</div>

On Fri, Sep 25, 2009 at 1:55 PM, Marnen Laibow-Koser  
[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Part has\_many :components, :through =\> lots
> 
> mypart.components
> 
> Done!

Light dawns on marble head.

Of course! Sorry for the noise.

–wpd

---

<div class="post-metadata">

**Author:** ![oldbug](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c0e974/32.png) [@oldbug](https://www.ruby-forum.com/u/oldbug)\
**Post date:** [September 25, 2009, 8:07pm UTC](https://www.ruby-forum.com/t/how-to-ensure-two-tables-reference-the-same-record-in-a-third-table/176174/4 "2009-09-25T20:07:14Z")

</div>

On Fri, Sep 25, 2009 at 1:59 PM, Patrick D. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> Of course! Sorry for the noise.  
> oh yeah, and thanks!

–wpd

---

<div class="post-metadata">

**Author:** ![oldbug](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c0e974/32.png) [@oldbug](https://www.ruby-forum.com/u/oldbug)\
**Post date:** [September 25, 2009, 8:40pm UTC](https://www.ruby-forum.com/t/how-to-ensure-two-tables-reference-the-same-record-in-a-third-table/176174/5 "2009-09-25T20:40:35Z")

</div>

Wow, so now I can do:

class Component \< ActiveRecord::Base

# Make component.part a shortcut for component.lot.part

def part(\*args)  
lot.send :part, args  
end  
end

…and do things like:

\<%= mycomponent.part.number %\>

…instead of …

\<%= mycomponent.lot.part.number %\>

nice!

–wpd

---

<div class="post-metadata">

**Author:** ![oldbug](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c0e974/32.png) [@oldbug](https://www.ruby-forum.com/u/oldbug)\
**Post date:** [September 28, 2009, 7:33am UTC](https://www.ruby-forum.com/t/how-to-ensure-two-tables-reference-the-same-record-in-a-third-table/176174/6 "2009-09-28T07:33:41Z")

</div>

delegate does precisely this…checkout

> **[Module](https://api.rubyonrails.org/classes/Module.html#M000110)**
>
> Attribute Accessors Extends the module object with class/module and instance accessors for class/module attributes, just like the native attr\* accessors for instance attributes.

---

<div class="post-metadata">

**Author:** ![oldbug](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c0e974/32.png) [@oldbug](https://www.ruby-forum.com/u/oldbug)\
**Post date:** [September 28, 2009, 12:55pm UTC](https://www.ruby-forum.com/t/how-to-ensure-two-tables-reference-the-same-record-in-a-third-table/176174/7 "2009-09-28T12:55:07Z")

</div>

Patrick D. wrote:

> Wow, so now I can do:
> 
> class Component \< ActiveRecord::Base
> 
> # Make component.part a shortcut for component.lot.part
> 
> def part(\*args)  
> lot.send :part, args

No need for send here:

def part(\*args, &block)  
lot.part(\*args, block) # may need an if block\_given?  
end

> end  
> end
> 
> …and do things like:
> 
> \<%= mycomponent.part.number %\>
> 
> …instead of …
> 
> \<%= mycomponent.lot.part.number %\>
> 
> nice!

Yup. Of course, you could have done that even without the :through.

> –wpd

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![oldbug](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c0e974/32.png) [@oldbug](https://www.ruby-forum.com/u/oldbug)\
**Post date:** [September 28, 2009, 8:45pm UTC](https://www.ruby-forum.com/t/how-to-ensure-two-tables-reference-the-same-record-in-a-third-table/176174/8 "2009-09-28T20:45:07Z")

</div>

> No need for send here:
> 
> def part(\*args, &block)  
> lot.part(\*args, block) # may need an if block\_given?  
> end

I was thinking that I would have to use #send since there is so much  
Ruby magic wrapped inside ActiveRecord in order to map column names to  
object attributes, but a very simple test (following your email)  
showed me I was wrong. (And it seems to work fine w/o block\_given? –  
I can’t find any ActiveRecord instance methods that take a block, so  
this might be overkill anyway.)

Basically, I wanted to add something like

belongs\_to :part, :through =\> :lot

to my Component model, which I was happy to see I could do with 3  
lines of code. I just picked 3 lines of code that were more  
complicated than they needed to be.

> Yup. Of course, you could have done that even without the :through.

I want to relationship to go both ways, and as far as I can tell,  
there is no belongs\_to :through =\> construct similar to the has\_many  
:through =\> construct. But it is easy enough to implement with the  
single method definition.

Thanks again for the help and the tips. I appreciate them both.

–wpd