Obviously, this is a design time factor, but, in my applications, I always use integer IDs if I'm ever going to bring the info that row contains (And other relevant info) to the UI. I have never had a solid reason to use GUIDs or UUIDs or whatever.
Any time I'm adding something to a listbox, combo box, or whatever it is, that element of that lists Object (RE: tStringList) gets the ID that is in the database. Using a UID would not work, unless I spend CPU cycles converting a 32 character string to bytes, then to 128-bit numbers. But then, my compiler is 32-bit only, but can emulate 64-bit numbers. I can see the reasoning why a UUID is appealing, but, an ID is an ID. It doesn't matter what it is. When you use INTEGER PRIMARY KEY, you get from 1 to 2^64-1 numbers to play with at LEAST. I don't know if SQLite will go into 128 or 256bit integers. With UUID, you're looking at a chance of collision. Small, yes. But its there. With INTEGER PRIMARY KEY, you're going up by one each time. Since I will never care what that ID is, as a developer or as a user, Integer IDs are perfect. On Fri, Nov 24, 2017 at 12:33 PM, Richard Damon <rich...@damon-family.org> wrote: > On 11/24/17 3:51 AM, R Smith wrote: > >> >> On 2017/11/24 5:23 AM, Peter Halasz wrote: >> >>> As for whether I need to use AUTOINCREMENT, it seemed like a good idea to >>> avoid rowid reuse, but I can avoid using it for the sake of optimization, >>> so probably will. >>> >> >> I agree with Keith and has many times mentioned this before (apologies to >> others for the déjà vu). You as the programmer / designer should always be >> in control of how and why a new ID is assigned. >> >> I would disagree here, in many cases the ID (rowid) is a purely internal > attribute with the purpose of accessing the data. It may have no problem > domain significance. If the primary key's purpose is purely data access, > then letting the data access layer handle it makes sense. The one case > where it makes sense for the programmer / designer to take control of the > PK is if the domain naturally has an identifier that would be suitable for > the key (an reasonable sized integer that is naturally unique), The > designer should also be fairly certain that it will remain so. > >> It feels like a saving to let the DB engine do it for you, but it isn't >> really. What you save in a bit of code that decides the new ID before-hand >> (which can be as simple as SELECT MAX(id)+1 FROM t) you typically have to >> catch up afterward in code to do the usual get-LastInsertedID and then pop >> that in wherever stuff wants to link to the new item. It's a zero-sum gain >> really. >> > The big issues with this method is if two processes both try to create a > new item at the same time, one of them is going to get an error and have to > redo its work. If you start by creating the record with an autoincrement > id, and then getting the ID used, then you remove the need to handle the > error on the simultaneous creation. > >> >> I'm not even a big fan of Integer IDs, I think codes / UUIDs are best, >> but obviously the speed/size gain with an integer key (especially INTEGER >> PRIMARY KEY row-id alias in SQLite) can't be ignored. >> >> Making a UUID or other 'big' key the primary access key will increase the > cost of looking up a record for ALL purposes. For small tables, it might > not be measurable, but small tables are less apt to need that sort of PK > either. It can make sense to use a key like that as an External Key to > describe the record to the outside world. Perhaps if the ONLY accesses to a > table are via this 'big' key, and very rarely by some other key/field, > making the big key the primary key would make sense. > >> Disclaimer: This is only my opinion, well, I'm not completely alone in >> it, but it is still an opinion and not a general SQL prescription. >> > > -- > Richard Damon > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
