# Linking two tables using a lookup table

**URL:** <https://www.ruby-forum.com/t/linking-two-tables-using-a-lookup-table/62593>\
**Category:** Rails\
**Created:** [June 6, 2006, 12:51pm UTC](https://www.ruby-forum.com/t/linking-two-tables-using-a-lookup-table/62593 "2006-06-06T12:51:08Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![hariraj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/4491bb/32.png) [@hariraj](https://www.ruby-forum.com/u/hariraj)\
**Post date:** [June 6, 2006, 12:51pm UTC](https://www.ruby-forum.com/t/linking-two-tables-using-a-lookup-table/62593/1 "2006-06-06T12:51:08Z")

</div>

Hi,

How do I associate two different tables which have a lookup id (table)  
as common.

I tried the has\_many :table2, :through =\> look\_up\_table in my table1  
model.

Any hints?

Thanks, Hari

---

<div class="post-metadata">

**Author:** ![hariraj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/4491bb/32.png) [@hariraj](https://www.ruby-forum.com/u/hariraj)\
**Post date:** [June 6, 2006, 3:02pm UTC](https://www.ruby-forum.com/t/linking-two-tables-using-a-lookup-table/62593/2 "2006-06-06T15:02:00Z")

</div>

some more info on the request:

Data model for the request…

Product table (table1)

- id
- name
- description
- price
- store\_id

Store table (lookup table)

- id
- name
- location

Invoice table (table2)

- id
- store\_id
- number
- amount

The product & invoice tables are using a lookup table i.e. (store table)

In this scenario how can I get the all the invoices for a particular  
product using the store\_id?  
e.g to get the first invoice amount…\> product.invoice[0].amount

When I use the “has\_many…through” the foreign key referenced is wrong.  
How should I define the association?

Thanks, Hari

---

<div class="post-metadata">

**Author:** ![hariraj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/4491bb/32.png) [@hariraj](https://www.ruby-forum.com/u/hariraj)\
**Post date:** [June 6, 2006, 4:41pm UTC](https://www.ruby-forum.com/t/linking-two-tables-using-a-lookup-table/62593/3 "2006-06-06T16:41:45Z")

</div>

You should look at has\_many :through again carefully. I would look at  
Josh S.'s excellent article on this topic for more information.

[http://blog.hasmanythrough.com/articles/2006/04/20/many-to-many-dance-off](http://blog.hasmanythrough.com/articles/2006/04/20/many-to-many-dance-off)

You’re mising some crucial fields in your linking table.

---

<div class="post-metadata">

**Author:** ![hariraj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/4491bb/32.png) [@hariraj](https://www.ruby-forum.com/u/hariraj)\
**Post date:** [June 6, 2006, 4:50pm UTC](https://www.ruby-forum.com/t/linking-two-tables-using-a-lookup-table/62593/4 "2006-06-06T16:50:40Z")

</div>

Brian H. wrote:

> You should look at has\_many :through again carefully. I would look at  
> Josh S.'s excellent article on this topic for more information.
> 
> [http://blog.hasmanythrough.com/articles/2006/04/20/many-to-many-dance-off](http://blog.hasmanythrough.com/articles/2006/04/20/many-to-many-dance-off)
> 
> You’re mising some crucial fields in your linking table.

Hi Brian, you mean I should treat the lookup table as a link table and  
add some keys?

I will go through the article, but I thought the model is fairly a  
simple one and a common practice to use a lookup table. Is there any  
other way to solve the problem?

Thanks, Hari

---

<div class="post-metadata">

**Author:** ![hariraj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/4491bb/32.png) [@hariraj](https://www.ruby-forum.com/u/hariraj)\
**Post date:** [June 7, 2006, 12:24pm UTC](https://www.ruby-forum.com/t/linking-two-tables-using-a-lookup-table/62593/5 "2006-06-07T12:24:11Z")

</div>

has\_many :through is for treating a lookup table as a linking table.

After reading your question again, here’s what you’re actually looking  
for.

The products table has a relationship to the store table via the  
foreign key store\_id

This means

class Product \< ActiveRecord::Base  
belongs\_to :store  
end

The same is true for Invoice

class Invoice \< ActiveRecord::Base  
belongs\_to :store  
end

This allows you to do  
@product = Product.find(1)  
@product.store.name

Now, say you have a store, and you want all of the products:

You need to define the has\_many associations on the store

class Store \< ActiveRecord::Base  
has\_many :products  
has\_many :invoices  
end

So now you can reference these.

@store = Store.find(1)  
@products = @store.products

for product in @products  
product.name  
end

belongs\_to always goes with the foreign key.  
If your product has a store\_id column, the product belongs\_to store  
If your store has a product\_id column, the store belongs\_to product (bad  
idea!!)

Don’t get this confused with has\_one. It’s rare that you would use  
has\_one.  
Become familiar with associations by reading the API or the Agile book.

---

<div class="post-metadata">

**Author:** ![hariraj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/4491bb/32.png) [@hariraj](https://www.ruby-forum.com/u/hariraj)\
**Post date:** [June 7, 2006, 2:40pm UTC](https://www.ruby-forum.com/t/linking-two-tables-using-a-lookup-table/62593/6 "2006-06-07T14:40:22Z")

</div>

oh, and sorry for the confusion! My mistake!

---

<div class="post-metadata">

**Author:** ![hariraj](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/4491bb/32.png) [@hariraj](https://www.ruby-forum.com/u/hariraj)\
**Post date:** [June 7, 2006, 2:23pm UTC](https://www.ruby-forum.com/t/linking-two-tables-using-a-lookup-table/62593/7 "2006-06-07T14:23:35Z")

</div>

On Jun 6, 2006, at 6:02 AM, Nara H. wrote:

> In this scenario how can I get the all the invoices for a particular  
> product using the store\_id?

With your model, you cannot tell which products are  
in which invoices, so you cannot do what you want to  
do.

–  
– Tom M.