# Tables: Unnecessary duplication of id fields

**URL:** <https://www.ruby-forum.com/t/tables-unnecessary-duplication-of-id-fields/141802>\
**Category:** Rails\
**Created:** [June 30, 2008, 6:17pm UTC](https://www.ruby-forum.com/t/tables-unnecessary-duplication-of-id-fields/141802 "2008-06-30T18:17:14Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![zakifataya](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/c89c15/32.png) [@zakifataya](https://www.ruby-forum.com/u/zakifataya)\
**Post date:** [June 30, 2008, 6:17pm UTC](https://www.ruby-forum.com/t/tables-unnecessary-duplication-of-id-fields/141802/1 "2008-06-30T18:17:14Z")

</div>

Hello,

I’m fairly new to Ruby, but I am trying to understand the relationship  
between database structure and models.

My difficulty is that I have five or so tables all linked together and  
their  
primary key column is a serial number

The tables are a set of tables called:

itemmasters  
products  
prices  
subprices  
imprints  
etc.

products, prices, and subprices, imprints, etc. belong to itemmasters  
based  
on a serial number

How can I get about linking them without having to have a column in  
itemmasters for each of the sub tables?

For instance:

product\_id  
price\_id  
subprice\_id  
imprint\_id

This would be somewhat silly as they all contain the same information.  
Right?

Thank you,

Brian A.

---

<div class="post-metadata">

**Author:** ![zakifataya](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/c89c15/32.png) [@zakifataya](https://www.ruby-forum.com/u/zakifataya)\
**Post date:** [June 30, 2008, 6:28pm UTC](https://www.ruby-forum.com/t/tables-unnecessary-duplication-of-id-fields/141802/2 "2008-06-30T18:28:54Z")

</div>

On Jun 30, 2008, at 9:17 AM, Brian A. wrote:

> The tables are a set of tables called:  
> on a serial number
> 
> This would be somewhat silly as they all contain the same information.  
> Right?

You could try playing around with primary\_key() to set the primary key  
for the given table as well as adjusting foreign\_key in your has\_one  
relationships to point to that primary key. Not sure if that will get  
you into trouble or not 🙂

---

<div class="post-metadata">

**Author:** ![zakifataya](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/c89c15/32.png) [@zakifataya](https://www.ruby-forum.com/u/zakifataya)\
**Post date:** [June 30, 2008, 6:51pm UTC](https://www.ruby-forum.com/t/tables-unnecessary-duplication-of-id-fields/141802/3 "2008-06-30T18:51:11Z")

</div>

In third normal relational design you would have the itemmaster\_id in  
each of the dependent tables.  
That is also what happens when you define the belongs\_to has\_many  
relationships.  
There needs to be some column in the database to relate the items.

What you bring up, each child id on the parent table is a star schema.

Otherwise you have to denormalize your data design and just stuff  
everything into one table.

On Jun 30, 11:17 am, Brian A. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![zakifataya](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/c89c15/32.png) [@zakifataya](https://www.ruby-forum.com/u/zakifataya)\
**Post date:** [June 30, 2008, 9:13pm UTC](https://www.ruby-forum.com/t/tables-unnecessary-duplication-of-id-fields/141802/4 "2008-06-30T21:13:13Z")

</div>

Rails expects the ID name to be a certain way, so if you use a  
different ID you have to map those columns.  
For a parent child relationship where the child belongs\_to a given  
parent. The default ID rais expects is parent\_id.  
For HABTM relationships you would need to create an intermediate  
table.

If you are just doing a straight select from a single table try using  
all lower case?

On Jun 30, 12:18Â pm, Brian A. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![zakifataya](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/c89c15/32.png) [@zakifataya](https://www.ruby-forum.com/u/zakifataya)\
**Post date:** [June 30, 2008, 7:18pm UTC](https://www.ruby-forum.com/t/tables-unnecessary-duplication-of-id-fields/141802/5 "2008-06-30T19:18:54Z")

</div>

Thanks, everyone for the responses. I think I am clearly conceptualizing  
things wrongly.

All the tables have an ItemSerial field

I am getting an error saying “Couldn’t find Itemprice without an ID”

This message comes up even despite the fact that this is my controller:

class ItempriceController \< ApplicationController  
def show  
@itemprices = Itemprice.find(params[:ItemSerial])  
end  
end

I am not finding Itemprice by id according to the above, but still I get  
this error.

In my view as a test I have the following:

\<% @page\_title = “#{@itemprices.Price1}” %\>

I may be thinking about this whole thing wrong.

-Brian

Jorg L. wrote:

> In third normal relational design you would have the itemmaster\_id in  
> each of the dependent tables.  
> That is also what happens when you define the belongs\_to has\_many  
> relationships.  
> There needs to be some column in the database to relate the items.
> 
> What you bring up, each child id on the parent table is a star schema.
> 
> Otherwise you have to denormalize your data design and just stuff  
> everything into one table.
> 
> On Jun 30, 11:17�am, Brian A. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![zakifataya](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/z/c89c15/32.png) [@zakifataya](https://www.ruby-forum.com/u/zakifataya)\
**Post date:** [June 30, 2008, 10:22pm UTC](https://www.ruby-forum.com/t/tables-unnecessary-duplication-of-id-fields/141802/6 "2008-06-30T22:22:05Z")

</div>

@Brian:

Your code says:

class ItempriceController \< ApplicationController

> def show  
> @itemprices = Itemprice.find(params[:ItemSerial])  
> end  
> end

The .find method on an ActiveRecord model finds by primary key. Without  
mapping your primary keys (either by doing it the way Rails wants you to  
or  
by overriding the primary key using the set\_primary\_key method, Rails  
will  
try to find by id.

Each field in your table also gets two finders automatically created for  
it. In your case, you have a field called item\_serial\_number or  
something  
simlar… let’s call it serialnumber for this example. With that field,  
Rails creates two new methods on the class:

Itemprice.find\_by\_serialnumber(15) (“select \* from itemprice where  
serialnumber = 15 LIMIT 1”)  
Itemprice.find\_all\_by\_serialnumber(15) (“select \* from itemprice where  
serialnumber = 15”)

So to get what you want, just do

class ItempriceController \< ApplicationController  
def show  
@itemprices = Itemprice.find\_by\_serialnumber(params[:ItemSerial])  
end  
end

You should grab some books on Ruby and Rails though… Rails can work  
with  
“legacy” schemas but it’s much more difficult if you’re just starting  
out.  
There are a lot of database conventions that Rails expects you to adhere  
to.

Good luck and I hope this helped a bit.