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
