>> For a simple common example, a page from a long list of users. To use >> $last_id_plus_1, I would first need a numeric id on my user table >> (unlikely, as the username is already a unique identifier so adding an >> autoincrement INT would be pointless > > been bitten by this many times. I add INTs as row ids no matter how unique > i think any data will be. Because sometimes... well, you know how it goes. > what if i have to change the username?
I've used this approach before, but it's definately not One-Size-Fits-All. You make a good point. It is possible to change a username in that case, by issuing an update statement to each table. This is not so bad, as long as it is a rare occurence. >> and break normalization > why would that be ? As I understand it: Creating a new unique identifier when you already have one, especially for it's own sake, breaks normalization. If usernames must be unqiue there's your identifier. Normalized doesn't always mean most efficient. > Moreover, if the username is your FK in any table, you're > wasting tons of space and possibly memory if the server is set right. It's true that an INT is more efficiently indexed than a CHAR by the DBMS, so that looks more efficient on the surface. However, this can be introducing more inefficiency than is immediately obvious. An example out of an application I maintain, reports are generated off sales records. These reports may be filtered and sorted by salesperson(FK username) or other username fields, and the PHB may want to see a table of rows including the salesperson, assigned customer service agent, perhaps underwriting agent or sales manager for each row. In this case, if I were to use an INT instead of a username for these fields, I would have to join the users table 4 times for each row to display the usernames! Now the slight efficiency gained using an INT user_id has become an efficiency problem when working with reports where the username need be known. Here again is an example that breaks the $id_plus_1 paging approach. If we sort by salesperson,date there is no usable $Last_PK_ID_+1 magic value to find the right page for this query. Or, if one can be somehow engineered, it will be a lot of work proprietary to only this one report, and the code will be more complex and harder to understand and modify when the maintenance coder comes around. /Mitchell K. Jackson _______________________________________________ List: [email protected] Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/[email protected]/ Dev site: http://dev.catalyst.perl.org/
