Jump to content

Natural or Surrogate Keys


TheFilmGod

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/192196-natural-or-surrogate-keys/
Share on other sites

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).

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.