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

KeithNYC@gmail.com 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,

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.

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.

KeithNYC@gmail.com 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

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

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.

augustlilleaas@gmail.com 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. :wink:

-Robby

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

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