TheFilmGod Posted February 15, 2010 Share Posted February 15, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/192196-natural-or-surrogate-keys/ Share on other sites More sharing options...
fenway Posted February 16, 2010 Share Posted February 16, 2010 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). Quote Link to comment https://forums.phpfreaks.com/topic/192196-natural-or-surrogate-keys/#findComment-1013148 Share on other sites More sharing options...
TheFilmGod Posted February 17, 2010 Author Share Posted February 17, 2010 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). Quote Link to comment https://forums.phpfreaks.com/topic/192196-natural-or-surrogate-keys/#findComment-1013534 Share on other sites More sharing options...
fenway Posted February 23, 2010 Share Posted February 23, 2010 It makes it incredibly easy to link tables together when there's only a single unique column value required. Quote Link to comment https://forums.phpfreaks.com/topic/192196-natural-or-surrogate-keys/#findComment-1016913 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.