Re: Mysql Rows

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux