[thelist] MySQL Tables for weblog

rudy r937 at interlog.com
Sat Mar 16 11:05:01 CST 2002

>> you might (it's just a thought) consider some kind of field
>> to hold the "permalink" anchors which all blogs have, because
>> the auto_increment fields aren't very good for that purpose
>Hey, Rudy - you care to expand on that a little? "permalink" anchors?

sure thing, bill, and thanks for asking, you know i love this stuff   ;o)

for an example of what permalinks are for, see any blog that uses them

basically, they allow you to bookmark blog entries and/or replies

they form the anchor portions of urls like this --


as for why an auto_increment field is a poor choice --

<tip type="rudy's database design rule of thumb #43">
never use an auto-generated surrogate primary key in an application -- not
ever, no way, never, no how -- because surrogate keys should be removable
or replaceable with no harmful effect on the application

i will defer explanation of what a surrogate key is, and why it's okay to
have them in primary keys (and therefore also foreign keys) but not outside
of that

since an auto_increment field is a surrogate key, this is sufficient to
make it a poor candidate for a permalink

consider that a permalink forms part of a url (omigod, maybe i should say
"an url"), and that, of necessity, it must have permanence, to avoid link

if you have ever (re)loaded, altered, copied, or otherwise manipulated a
database table containing an auto_increment column, you will have learned
not to rely on the permanence of autonumbers -- letting autonumber columns
get renumbered is an option you want to leave open

you don't want your urls tied to a number that now has to remain unchanged

we made that mistake, by the way, in the evolt database -- but fortunately
in oracle the numbers aren't assigned automatically, rather they are "drawn
from" a sequence, a stack of numbers, and this is done by the application
sql (which is really neat, but you have to be a wacko like me to appreciate
it), so it doesn't matter



More information about the thelist mailing list