Using url sections as primary keys?
Moderator: General Moderators
- allspiritseve
- DevNet Resident
- Posts: 1174
- Joined: Thu Mar 06, 2008 8:23 am
- Location: Ann Arbor, MI (USA)
Using url sections as primary keys?
As a habit I generally use an auto incrementing ID as my primary keys. However, I'm finding more and more scenarios where using the url section (whatever uniquely identifies, say, a page in the url) as a primary key would save a join.
For instance, in our in house CMS, we have "tabs" and "pages". A url for a page looks like this: /:tab_name/:page_name. In order to display a link to a page, I have to join the tab table to the page table to get the url section for each tab.
Is this common? Is there a reason why I *wouldn't* want to use url sections as a primary key? Maybe they should be a non primary unique key, but still used as a foreign key?
For instance, in our in house CMS, we have "tabs" and "pages". A url for a page looks like this: /:tab_name/:page_name. In order to display a link to a page, I have to join the tab table to the page table to get the url section for each tab.
Is this common? Is there a reason why I *wouldn't* want to use url sections as a primary key? Maybe they should be a non primary unique key, but still used as a foreign key?
Re: Using url sections as primary keys?
Integers index faster and are easier to deal with.
- allspiritseve
- DevNet Resident
- Posts: 1174
- Joined: Thu Mar 06, 2008 8:23 am
- Location: Ann Arbor, MI (USA)
Re: Using url sections as primary keys?
Ok so what would you recommend then? Joins as I've been doing, or nonprimary unique keys?JakeJ wrote:Integers index faster and are easier to deal with.
Re: Using url sections as primary keys?
I'm interested in hearing why is thatIntegers index faster
Last edited by Eran on Mon May 10, 2010 12:49 pm, edited 1 time in total.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Using url sections as primary keys?
It seems like there are a couple different issues here. One is the question of whether integer keys are faster than char/varchar keys. I honestly doubt there is much difference -- especially if you are doing a join.
A second question is whether you should de-normalize your schema by combining your tabs and pages data into a single table with a combined key. That could certainly make sense, especially if it is frequently accessed data of which you control the construction.
And as always, the question is: Do you have an actual performance problem?
And even if you are, there may be better solutions, such as caching the results based on tab/page. That way you keep your database and logic design clean.
A second question is whether you should de-normalize your schema by combining your tabs and pages data into a single table with a combined key. That could certainly make sense, especially if it is frequently accessed data of which you control the construction.
And as always, the question is: Do you have an actual performance problem?
(#10850)
- allspiritseve
- DevNet Resident
- Posts: 1174
- Joined: Thu Mar 06, 2008 8:23 am
- Location: Ann Arbor, MI (USA)
Re: Using url sections as primary keys?
If I had my way, we'd do away with tabs and just have a hierarchy of pages. Don't get me started on that, thoughChristopher wrote:A second question is whether you should de-normalize your schema by combining your tabs and pages data into a single table with a combined key. That could certainly make sense, especially if it is frequently accessed data of which you control the construction.
No, I don't have a performance problem. I'm asking because since I've started working with clean URLs, I seem to be pulled towards using the url sections (or "slugs", though I hate that name) as keys. It seems like it would make things simpler, as is usually the case with good design.Christopher wrote:And as always, the question is: Do you have an actual performance problem?And even if you are, there may be better solutions, such as caching the results based on tab/page. That way you keep your database and logic design clean.
Re: Using url sections as primary keys?
I answered this but it didn't show up for some reason.pytrin wrote:I'm interested in hearing why is thatIntegers index faster
Maybe it's just a habit from my early days doing MS Access development. Slower processes and a crappy engine maybe is what dictated that. All the Access design books always recommended using integers as primary keys and I've just always done it that way except for a few times when I wasn't too concerned about style, etc.
I also found this link: http://dbaspot.com/forums/postgresql/31 ... rchar.html
Re: Using url sections as primary keys?
well... I have a similar experience coming from the Oracle word and the main technical reason that I did learn back then (could be no necessarily true today) was:JakeJ wrote:I answered this but it didn't show up for some reason.pytrin wrote:I'm interested in hearing why is thatIntegers index faster
Maybe it's just a habit from my early days doing MS Access development.
- Storage : "The smaller your Datatype, then more records will fit in your index blocks, therefore, fitting more records in each block fewer reads will be necessary to access your data, hence faster access"
then again... maybe that rule is not important nowadays or you can go over using different techniques... after all I'm just old... but ready to learn more
Re: Using url sections as primary keys?
Yes, it's true that a smaller index will be faster to seek and traverse. I wouldn't think there's any noticeable difference in indexing speed though.
In any case, for small URL parts it would be comparable or even smaller than an Integer, depending on the types. http://dev.mysql.com/doc/refman/5.0/en/ ... ments.html
Probably not a good reason to choose an integer type when the circumstances suggest using a native key. An important reason not to use a URL part as a primary key in my opinion, is that they are liable to change. Primary keys should be a value that would never change, for integrity reasons.
In any case, for small URL parts it would be comparable or even smaller than an Integer, depending on the types. http://dev.mysql.com/doc/refman/5.0/en/ ... ments.html
Probably not a good reason to choose an integer type when the circumstances suggest using a native key. An important reason not to use a URL part as a primary key in my opinion, is that they are liable to change. Primary keys should be a value that would never change, for integrity reasons.