Using url sections as primary keys?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Using url sections as primary keys?

Post by allspiritseve »

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?
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Using url sections as primary keys?

Post by JakeJ »

Integers index faster and are easier to deal with.
User avatar
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?

Post by allspiritseve »

JakeJ wrote:Integers index faster and are easier to deal with.
Ok so what would you recommend then? Joins as I've been doing, or nonprimary unique keys?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Using url sections as primary keys?

Post by Eran »

Integers index faster
I'm interested in hearing why is that
Last edited by Eran on Mon May 10, 2010 12:49 pm, edited 1 time in total.
User avatar
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?

Post by Christopher »

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.
(#10850)
User avatar
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?

Post by allspiritseve »

Christopher 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.
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, though :)
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.
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.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Using url sections as primary keys?

Post by JakeJ »

pytrin wrote:
Integers index faster
I'm interested in hearing why is that
I answered this but it didn't show up for some reason.

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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Using url sections as primary keys?

Post by mikosiko »

JakeJ wrote:
pytrin wrote:
Integers index faster
I'm interested in hearing why is that
I answered this but it didn't show up for some reason.

Maybe it's just a habit from my early days doing MS Access development.
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:
- 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 :)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Using url sections as primary keys?

Post by Eran »

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.
Post Reply