Re: Mysql Rows

----- Original Message ----- From: "tedd" <tedd@sperling.com> To: <php-general@lists.php.net> Cc: "benifactor" <snorris17@cox.net>; "Murray @ PlanetThoughtful" <lists@ulblog.org>; "Anthony Ettinger" <aettinger@sdsualumni.org> Sent: Saturday, March 04, 2006 3:14 PM Subject: Re: [PHP] 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