Topic: Natural or Surrogate Keys

[TheFilmGod]

I am currently in the process of finalizing my database. Before I dive head first into the massive programming job that still awaits me, I was hoping someone can help me decide whether using natural keys in my database is appropriate.

Using natural keys really depends on the situation. Here is an example:

* denotes a field of the primary key

ec_comments

* network_id
* ec_id
* comment_id
author_id
datetime
text
... and some other fields

ec_comments_reply

* network_id
* ec_id
* comment_id
* reply_id
author_id
text

As you can see the primary keys are natural and tell the developer (me) a lot about what I am querying. The concern I have is that to query and find a specific reply to a comment, I need to know the network id, ec_id, comment_id, and reply id. I can't simply know the comment_id and the reply id. Therefore to see a comment the url address would be something like.... www.mywebsite.com/comment.php?network=1&ec_id=3&comment_id=24&reply_id=2

So what do you think? Should I use natural keys or simplify everything and use a surrogate key?

[fenway]

I'm all about surrogate keys -- having a UID for each record in a table is priceless, and adds little overhead (at least for non-clustered indexes).

[TheFilmGod]

I'm all about surrogate keys -- having a UID for each record in a table is priceless, and adds little overhead (at least for non-clustered indexes).

Can you please elaborate why surrogate keys are advantageous to a natural keys. Surrogate keys hold no value and in my particular scenario, would create significant overhead as I will be using innodb (clustered indexes).

[fenway]

It makes it incredibly easy to link tables together when there's only a single unique column value required.