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? 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). 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). 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. Link to comment https://forums.phpfreaks.com/topic/192196-natural-or-surrogate-keys/#findComment-1016913 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.