----- Original Message -----
From: "tedd" <tedd@xxxxxxxxxxxx>
To: <php-general@xxxxxxxxxxxxx>
Cc: "benifactor" <snorris17@xxxxxxx>; "Murray @ PlanetThoughtful"
<lists@xxxxxxxxxx>; "Anthony Ettinger" <aettinger@xxxxxxxxxxxxxx>
Sent: Saturday, March 04, 2006 3:14 PM
Subject: Re: Mysql Rows
planetthoughtful wrote:
But, too often I've seen people new to database design not liking 'gaps'
because 'user1' will have a unique id of '1', while 'user2' will have a
unique id of '6' because the records associated with unique ids '2'
through '5' were deleted during testing, and so on. So, they feel that
'user2' should have a unique id of '2', ignoring the fact that that's not
a unique id at all, if you had id '2' associated with another record at
some point.
And, Anthony wrote:
I remember the days where i'd
clear a database after testing to keep the auto_increment inline, but
eventually, you will get out of sync on that, so it's not a reliable way
of
keeping a numerical sequence.
Well... I'm one of those people who don't like gaps. I understand that if
the dB is relational, then you shouldn't be concerned about gaps. Gaps are
only perceived from a perspective of an artificial ordering system -- who
knows where the data actually is in memory or on disk.
However, when I'm working with a flat dB and want to step through the
records to do editing, I like the records to be in order based upon an
"id" (i.e., Record 1, Record 2, Record 3, and so on). I use an
auto_increment unique "id" for this.
It's not a big problem for me to keep the records in order either.
Whenever I delete a record, I simply follow with:
$dbQuery = "ALTER TABLE $dbtable ";
$dbQuery .= "DROP id, ";
$dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
$dbQuery .= "AUTO_INCREMENT = 1";
$result = mysql_query($dbQuery) or die("2. Could not renumber dB $dbQuery"
. mysql_error());
and my dB is in order and all things are right with the world again. I'm
simple-minded that way.
Now, I'm not allowing more one than one person (namely me) the ability to
delete and reorder things, so I don't think there are any problems. Of
course I could lock down the tables, delete, and then do the reorder if
the dB is online -- but I haven't encountered any problems thus far.
I've read numerous dB books about why it isn't necessary to reorder and
everyone deplores the action, which is only done by newbies. But I don't
really understand, with a flat dB, as to why it's a bad idea to do this?
Now, is there a problem with the way I'm doing this? If so, *please*
enlighten me. Please tell me why this isn't a reliable way of keeping a
numerical sequence AND what technique would be?
Many thanks.
tedd
--
--------------------------------------------------------------------------------
http://sperling.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Hi Tedd!
Generally speaking about db's it's not a good pratice to do in that you
describe, but as I understand you've already figured that out...
But one thing not doing "your" way is lack of performance:
I'll quote you:
"not a big problem for me to keep the records in order either.
Whenever I delete a record, I simply follow with:
$dbQuery = "ALTER TABLE $dbtable ";
$dbQuery .= "DROP id, ";
$dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
$dbQuery .= "AUTO_INCREMENT = 1";
$result = mysql_query($dbQuery) or die("2. Could not renumber dB $dbQuery"
. mysql_error());"
No, maybe not when it's a small db, but when you try to delete 50.000 posts
I have a strong feeling this would be very much slower then if you don't
alter table after each deletion.
/G
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
