Primary Keys

Rails
1

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

2

[email protected] 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 R.
http://www.robbyonrails.com/

3

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, [email protected] [email protected] wrote:

be the PK? Is that going to break anything?

Thanks,
-Keith

–
Sterling A.
sterling [at] sterlinganderson.net
http://sterlinganderson.net/
608.239.8387

4

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.

5

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

6

[email protected] wrote:

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 R.
http://www.robbyonrails.com/

7

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.

8

[email protected] 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?

You’ll be wishing you had a unique intger index then. :wink:

-Robby

–
Robby R.
http://www.robbyonrails.com/

9

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

10

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 :slight_smile:

Thanks again - Keith