Rails - Jan 2007 - Primary Keys

Hi,

Is it absolutely necessary to call the PK in a table ''id''? My
project
manager who is also doing DB design for a new project that I''m trying
to push using RoR seems to take serious issue with that convention.
Mainly since the PK in Table1 would be called just ''id'', but
when its a
FK in table2 it would have a different name.

Is it enough to simply set id to auto increment but have another column
be the PK? Is that going to break anything?

Thanks,
-Keith


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

KeithNYC-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:
> Hi,
> 
> Is it absolutely necessary to call the PK in a table
''id''? My project
> manager who is also doing DB design for a new project that I''m
trying
> to push using RoR seems to take serious issue with that convention.
> Mainly since the PK in Table1 would be called just ''id'',
but when its a
> FK in table2 it would have a different name.
> 
> Is it enough to simply set id to auto increment but have another column
> be the PK? Is that going to break anything?
What does the project manager propose the PK column be named? If the 
answer is just another naming convention, but still an auto incrementing 
integer, than this is a simple fix in your models.

If the project manager is encouraging a composite key or something else, 
I''d like to understand the argument for it.

Cheers,

Robby

-- 
Robby Russell
http://www.robbyonrails.com/
http://www.planetargon.com/

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Robby,

An example would be a States table. He has a problem with not having
the PK be the 2 char state code, as opposed to some autoincremented ID.

In another example, we have an Orders table which has a child
Order_Items table. The PK in Orders would be order_id, and the FK in
Order_Items would be order_id. The issue is calling the PK in Orders
just ''id'', but having it be called order_id when it is a FK
elsewhere.

Personally I think it''s nitpicking, whereas he believes we are allowing
the development tool to dictate the data model.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

If you are pushing RoR you might want to try to educate your project
manager a bit on it. I don''t know how good of an idea it would be to
have someone doing DB design for a Rails project who doesn''t have at
least a cursory knowledge of the idea behind Rails and how it works.
Many of the notions your PM may have about DB design could be sort of
tossed to the side by Rails. Particularly in the area of
primary/foreign/composite keys.

On 1/22/07, KeithNYC-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
<KeithNYC-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> Hi,
>
> Is it absolutely necessary to call the PK in a table
''id''? My project
> manager who is also doing DB design for a new project that I''m
trying
> to push using RoR seems to take serious issue with that convention.
> Mainly since the PK in Table1 would be called just ''id'',
but when its a
> FK in table2 it would have a different name.
>
> Is it enough to simply set id to auto increment but have another column
> be the PK? Is that going to break anything?
>
> Thanks,
> -Keith
>
>
> >
>

-- 
Sterling Anderson
sterling [at] sterlinganderson.net
http://sterlinganderson.net/
608.239.8387

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

KeithNYC-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:
> Robby,
> 
> An example would be a States table. He has a problem with not having
> the PK be the 2 char state code, as opposed to some autoincremented ID.
> 
> In another example, we have an Orders table which has a child
> Order_Items table. The PK in Orders would be order_id, and the FK in
> Order_Items would be order_id. The issue is calling the PK in Orders
> just ''id'', but having it be called order_id when it is a
FK elsewhere.
> 
> Personally I think it''s nitpicking, whereas he believes we are
allowing
> the development tool to dictate the data model.
> 
Well, to be fair... he''s being opinionated. Unfortunately, Rails is
also
opinionated, so there needs to be some give and take. I feel the same 
way when it comes to the development structure.. the user experience 
_should_ dictate the application design, which dictates the data model. 
That''s just my opinion though.

I think it''s more confusing to name your primary key the same as you 
would a foreign key.

table orders
   id

table order_items
   id
   order_id

Since Rails _should_ be the only thing touching your database directly, 
what''s the issue?

Again, good luck!

-Robby


-- 
Robby Russell
http://www.robbyonrails.com/
http://www.planetargon.com/

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

The reason rails uses an auto-incrementing primary key is because of the
performance benefits. In large databases, the difference between looking
something up by number and looking it up by something else can be huge.
-Nathan

On 22/01/07, Robby Russell
<robby.lists-/Lcn8Y7Ot69QmPsQ1CNsNQ@public.gmane.org>
wrote:
>
>
> KeithNYC-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:
> > Robby,
> >
> > An example would be a States table. He has a problem with not having
> > the PK be the 2 char state code, as opposed to some autoincremented
ID.
> >
> > In another example, we have an Orders table which has a child
> > Order_Items table. The PK in Orders would be order_id, and the FK in
> > Order_Items would be order_id. The issue is calling the PK in Orders
> > just ''id'', but having it be called order_id when it
is a FK elsewhere.
> >
> > Personally I think it''s nitpicking, whereas he believes we
are allowing
> > the development tool to dictate the data model.
> >
>
> Well, to be fair... he''s being opinionated. Unfortunately, Rails
is also
> opinionated, so there needs to be some give and take. I feel the same
> way when it comes to the development structure.. the user experience
> _should_ dictate the application design, which dictates the data model.
> That''s just my opinion though.
>
> I think it''s more confusing to name your primary key the same as
you
> would a foreign key.
>
> table orders
>    id
>
> table order_items
>    id
>    order_id
>
> Since Rails _should_ be the only thing touching your database directly,
> what''s the issue?
>
> Again, good luck!
>
> -Robby
>
>
> --
> Robby Russell
> http://www.robbyonrails.com/
> http://www.planetargon.com/
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Tell your project manager to go read some books about databases.
Primary keys are primary keys, and just that. A 2 char state code is a
2 char state code, and just that. You can NEVER know wether or not
values change, and that 2 char state code is a value. It''s very
unlikely, but what if a state changes it''s name? It may sound very
silly, but you really do never never ever know if a value can change or
not. That''s why you have the ID column, as that column is a primary
key, not a value.

On Jan 22, 5:04 pm, njmacin...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
wrote:
> The reason rails uses an auto-incrementing primary key is because of the
> performance benefits. In large databases, the difference between looking
> something up by number and looking it up by something else can be huge.
> -Nathan
>
> On 22/01/07, Robby Russell
<robby.li...-/Lcn8Y7Ot69QmPsQ1CNsNQ@public.gmane.org> wrote:
>
>
>
> > Keith...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:
> > > Robby,
>
> > > An example would be a States table. He has a problem with not
having
> > > the PK be the 2 char state code, as opposed to some
autoincremented ID.
>
> > > In another example, we have an Orders table which has a child
> > > Order_Items table. The PK in Orders would be order_id, and the FK
in
> > > Order_Items would be order_id. The issue is calling the PK in
Orders
> > > just ''id'', but having it be called order_id
when it is a FK elsewhere.
>
> > > Personally I think it''s nitpicking, whereas he believes
we are allowing
> > > the development tool to dictate the data model.
>
> > Well, to be fair... he''s being opinionated. Unfortunately,
Rails is also
> > opinionated, so there needs to be some give and take. I feel the same
> > way when it comes to the development structure.. the user experience
> > _should_ dictate the application design, which dictates the data
model.
> > That''s just my opinion though.
>
> > I think it''s more confusing to name your primary key the same
as you
> > would a foreign key.
>
> > table orders
> >    id
>
> > table order_items
> >    id
> >    order_id
>
> > Since Rails _should_ be the only thing touching your database
directly,
> > what''s the issue?
>
> > Again, good luck!
>
> > -Robby
>
> > --
> > Robby Russell
> >http://www.robbyonrails.com/
> >http://www.planetargon.com/

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

augustlilleaas-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
wrote:
> Tell your project manager to go read some books about databases.
> Primary keys are primary keys, and just that. A 2 char state code is a
> 2 char state code, and just that. You can NEVER know wether or not
> values change, and that 2 char state code is a value. It''s very
> unlikely, but what if a state changes it''s name? 
I''m a big advocate of using an id field in any database table... just 
for this reason. What happens when those of us in the Pacific NW finally 
leave the US and form the Republic of Cascadia?

* http://zapatopi.net/cascadia/
* http://en.wikipedia.org/wiki/Cascadia

You''ll be wishing you had a unique intger index then. ;-)

-Robby

-- 
Robby Russell
http://www.robbyonrails.com/
http://www.planetargon.com/

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Hi Keith,

I mocked up an example for you. Let''s say you have a schema named in a
non-standard rails way. Your Orders table is named Orderz and the
primary key is called "order_id" instead of the rails convention of
"id".

Also, your Order_items table happens to be named the standard rails way
but your primary key is "order_items_id" instead of "id" and
the
foreign key back to the Orderz table is called "order_id_FK" instead
of
"order_id"..

You would then have database tables defined as:

 Orderz
   order_id (primary key)
   order_column_whatever

 Order_items
  order_items_id (primary key)
  order_qty
  order_id_FK (foreign key back to Orderz)

And your model classes would be defined like this:

 class Order < ActiveRecord::Base
    set_table_name "orderz"
    set_primary_key "order_id"
    has_many :order_items, :foreign_key => "order_id_FK"
 end

 class OrderItem < ActiveRecord::Base
    set_primary_key "order_items_id"
    belongs_to :order, :foreign_key => "order_id_FK"
 end

Then you can do:
  A. items = Order.find(1).order_items
      generates: SELECT * FROM order_items WHERE
(order_items.order_id_FK = 1)

  B. items.first.order
      generates: SELECT * FROM orderz WHERE (orderz.order_id = 1)
LIMIT 1

  C. order = Order.find(1)
      new_item = OrderItem.new(:order_qty => 99)
      order.order_items << new_item
      generates: INSERT INTO order_items (`order_id_FK`, `order_qty`)
VALUES(1, 99)

As far as composite keys go, I would strongly recommend defining a
single primary key on the table (call it whatever you want) and then
define a unique index on the composite keys. This will allow you to
easily stay within the rails framework but allow for fast retrieval of
rows based on the composite keys when required.

Paul


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Thanks for all the information guys - and thanks Paul for that mock up,
it''s appreciated!

I''m trying to pick my battles here - I compromised on going with SQL
Server over MySQL (which has caused tons of issues with hosting; hard
to find a host that allows you to connect to MySQL from Linux, or a
Windows based host that hosts RoR apps). So hopefully he''ll give on
this convention thing :)

Thanks again - Keith


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---