Re: Why do we avoid auto-incrementing db columns?

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 do believe in db generated id's for
non-application/non-information
columns. E.g. the watchdog table; information is just
inserted into the
database, and then read later, so the id field isn't really
used can be
blind to the application. 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.

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".


-----Original Message-----
From: development-bouncesdrupal.org
[mailto:development-bouncesdrupal.org]
On Behalf Of Mark Fredrickson
Sent: Wednesday, April 25, 2007 11:31 AM
To: developmentdrupal.org
Subject: Re: [development] Why do we avoid auto-incrementing
db columns?

I'd like to point out this is a really discussion about
mysql, not
about other database. Postgresql Drupal installations
haven't used a
sequences table ever, IIRC. As long as each database can
implement
some equivalent of db_next_id() -- be it by a sequences
table, auto
incremented values, Postgres's CURVAL() function, whatever
-- Drupal's
db abstraction will work.

So let's rephrase: do the currently supported versions of
mysql all
support some sort of db_next_id() functionality? What about
Drupal 6?

If so, the sequences table can be dropped in favor of
auto_increment.

-M

Hi Earnest and others,

On 4/25/07, Earnest Berry III <earnest.berrygmail.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