[development] Why do we avoid auto-incrementing db columns?

[Mark Fredrickson]

Hi Earnest and others,

On 4/25/07, Earnest Berry III <earnest.berry at gmail.com> wrote:
> Hmm...I'm still more of a fan of controlling the IDs application level
> Than using a db generated serial number, especially when dealing with
> referential integrity.

I have the opposite standpoint. The more referential integrity that is
in the app layer, the harder it it is to integrate and share data from
the database. Use case: I have a another (non-PHP) system that needs
to insert nodes. If it can't generate a valid ID for that node, this
process is halted in it's tracks.

I would like to see more data modeling logic moved to the db layer
(stored procedures, triggers, views, etc), and leave PHP to do the
theming and IO on top of that. E.g. CCK would be a great candidate for
going to the db layer.

> However, say an NID, of the txid (from e-commerce)
> gets passed around drupal and used by other modules, sometimes in loops, and
> sometimes for other modules to ref. the node; this I'd rather have some
> control of.

I do not see why these behaviors indicate that referential integrity
should be handled in the app. If you are saying that within a loop,
one must generate N IDs, each with a database call, there is some
merit to argument that app created IDs would be better. But there are
going to be other tradeoffs.

> Also, the implementations of "next_val" per-database get a little hairy
> also. You also then have the issue of if you are in a transaction, the
> transaction level, etc. that all starts to play in to the db's
> implementation of "next_val" or "last_insert_id".

I'll defer to others' experience on this subject, but I'd be surprised
if the problems were intractable. Considering that Drupal currently
supports all of 2 databases (I'm crossing my fingers for SQLite
support - that'd be cool) unifying the implementations shouldn't be
that hard.

-M