ID as primary key

Rails
1

Good day everyone,

First of all, I am fairly new to RoR and am currently reading “Agile Web
Development with Rails” book to get me started. I guess I am a little
confuse as why we are encouraged to use “ID” as a PK for most, if not
all, our tables. In the book page# 206 (for those who has one), it talks
about why ORDER table has a PK of ID instead of something such as
order_number and some other meaningful column. Could someone explain to
me why using “ID” as PK is a better solution than let’s say combining
PKs from 2 different tables for a linking table?

The book provides an example and reasoning for why we should use “ID”
versus “ISBN” number as a PK for a table “BOOK”. It says that if the
ISBN formating were to change (assuming there is another table that
reference rows in book table), we will have to drop the FK constraint,
then update the ISBN in both tables and finally recreate the FK
constraint. I seriously thought that this constraint is the point of
implementing relationship between tables so that people cant just go in
to the DB and start changing ISBN number without doing all the dropping
FK, updating ISBN, etc?

Please advise and thank you in advance,

Will

2

Hi Will:

It’s not just a Ruby on Rails concept. This is well-travelled territory,
here in this forum and anywhere else on the internet related to database
programming. /both ways of doing it have their pros and cons. Google
“primary key surrogate key” or “natural key surrogate key” or
“intelligent key surrogate key” and you’ll find plenty of discussions
espousing both sides of the issue. After that, it’s pretty much up to
you to decide for yourself how to do things.

c.

Will wrote:

Good day everyone,

First of all, I am fairly new to RoR and am currently reading “Agile Web
Development with Rails” book to get me started. I guess I am a little
confuse as why we are encouraged to use “ID” as a PK for most, if not
all, our tables. In the book page# 206 (for those who has one), it talks
about why ORDER table has a PK of ID instead of something such as
order_number and some other meaningful column. Could someone explain to
me why using “ID” as PK is a better solution than let’s say combining
PKs from 2 different tables for a linking table?

The book provides an example and reasoning for why we should use “ID”
versus “ISBN” number as a PK for a table “BOOK”. It says that if the
ISBN formating were to change (assuming there is another table that
reference rows in book table), we will have to drop the FK constraint,
then update the ISBN in both tables and finally recreate the FK
constraint. I seriously thought that this constraint is the point of
implementing relationship between tables so that people cant just go in
to the DB and start changing ISBN number without doing all the dropping
FK, updating ISBN, etc?

Please advise and thank you in advance,

Will

3

Hey Cayce,

Thank you so much for responding to my questions. Your comment is really
beneficial for me to move further with RoR development. Again, thank you
so much for the insight.

With much appreciation,

Will

Cayce B. wrote:

Hi Will:

It’s not just a Ruby on Rails concept. This is well-travelled territory,
here in this forum and anywhere else on the internet related to database
programming. /both ways of doing it have their pros and cons. Google
“primary key surrogate key” or “natural key surrogate key” or
“intelligent key surrogate key” and you’ll find plenty of discussions
espousing both sides of the issue. After that, it’s pretty much up to
you to decide for yourself how to do things.

c.

Will wrote:

Good day everyone,

First of all, I am fairly new to RoR and am currently reading “Agile Web
Development with Rails” book to get me started. I guess I am a little
confuse as why we are encouraged to use “ID” as a PK for most, if not
all, our tables. In the book page# 206 (for those who has one), it talks
about why ORDER table has a PK of ID instead of something such as
order_number and some other meaningful column. Could someone explain to
me why using “ID” as PK is a better solution than let’s say combining
PKs from 2 different tables for a linking table?

The book provides an example and reasoning for why we should use “ID”
versus “ISBN” number as a PK for a table “BOOK”. It says that if the
ISBN formating were to change (assuming there is another table that
reference rows in book table), we will have to drop the FK constraint,
then update the ISBN in both tables and finally recreate the FK
constraint. I seriously thought that this constraint is the point of
implementing relationship between tables so that people cant just go in
to the DB and start changing ISBN number without doing all the dropping
FK, updating ISBN, etc?

Please advise and thank you in advance,

Will

4

On Sun, 2006-11-05 at 18:38 +0100, Will wrote:

Please advise and thank you in advance,

it’s one of the things that rails defaults to in order to simplify
things.

Essentially, if the ‘id’ is an untouched primary key without
significance to the application itself, you are free to operate on all
of the fields and editing a particular item itself is easily
accomplished and at worst, you have one integer field extra.

If the primary key is a field that you need to modify, then you have to
do some gymnastics within your code in order to save the record (and all
associated records) with a new primary key which sort of defeats the
simplification that makes rails work.

Once you start developing a rails application, it clicks in and it
becomes obvious.

Craig

5

Hi Will,

Welcome to wonderful world of RoR. This incremental ID is probably
the most asked, most answered, most criticized, most … etc issue in
Rails. I’ll give you the answer that I have given countless times.

What makes Rails unique is its approach of “convention over
configuration”. Id is an example of this. Consider you have an entity
called Order with PK id. And another model called Invoice. You might
normally do lots of coding to match those and prevent users from
breaking the link. But in rails, order_id in Invoice will be treated
as a natural link to an order and you’ll be able to 1) Define the
relationship, 2) declare the restrictions rather than code every case
3) Be able to access any time. i.e. Invoice.order.date to access the
order date.

The convention over configuration not only prevents you from typing
but helps you by putting constraints in front of you. Constraints, in
coding, regardless of the implied meaning is actually good for your
health. Because you don’t have to make decisions when you have
constraints. You’ll not think while you’re coding to remember if
some field was named tracking_number, tracking_id, track_No etc.
You’ll implicitly write tracking_number and go.

This may sound a bit limiting, and indeed it is. But there’s a trade-
off. You either will stick with the conventions and constrains
implied by them or you’ll have to think about them, document them,
track them, test them, communicate them, and miss the fun part.
Well, the fun part is, rails have evolved as no other web based
technology have by putting one brick over the other based on these
conventions. If you stick with conventions, you’ll be somewhat future-
proof. Just to give you an example, if you had a rails project
complying with the conventions, you may easily convert it to be a
fully-fledged REST web service when it comes up in the new version.
(BTW you can do it now if you’re using Edge Rails, which you’ll need
to be in order to follow the Rails Book v2 )

To cut the long story short, Rails’ power (mostly) lies in the
approach. Rails and Ruby is flexible and powerful if you want to
divert from the convention. BUT until your next project. Also don’t
forget the fact that Rails is best for database backed web applications.

Having said those, I need to add that I’ve been developing web apps
starting with CGI-C, Server side Javascript, Perl, PHP, Java (EE and
non-EE). The productivity boost is not even close with what I’ve
experienced with Rails.

Disclaimer. Not every type of project is suitable for Rails. You’ll
find yourself going back and forth to other tools. But I hope you’ll
not be disappointed because Rails and the community in general does
not see it as a silver bullet.

Hope this helps.

Regards,
Oyku.