[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 --
http://example.com/archive.html?permalink=8wBx245G
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
</tip>
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
rot
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
forever
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
helps?
rudy
More information about the thelist
mailing list