Primary Keys

rubyonrails-talk
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

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

3

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.

4

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.

5

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

6

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

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

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?

* The Republic of Cascadia * Cascadia - Wikipedia

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

-Robby

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