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
