Re: [Info-ingres] Re: is there an equivavlent to auto_increment in ingres ?

"Paul Andrews" <ac297@xxxxxxxxxxxxxxxxxx> wrote in message
news:nEpDe.1408$Cu3.1233@xxxxxxxxxxxxxxxxxxxxxxx
> > I don't want to pick an endless stream of specific examples apart, so I
> will
> > just say that there are a number of appealing-sounding intuitions here
> that
> > actually require careful justification. It is not at all clear to me
that
> > this is a plausible example of where a synthetic key is required. For a
> > start, what is the intrinsic significance of 456 to the user? It has
none
> > at all.
>
> The user understands the notion of a queue of jobs to be performed and
> understands the idea that 456 uniquely identifies their task in the
queue -
> they may have 20or 30 tasks in progress. If need be they can pick up the
> phone and discuss their job with a helpline, administrator or work
> colleague.

I'm still not getting it. I won't assume there is nothing to get, but I
need it explained to me some more. What does the job number itself tell the
user?

> In an ideal world maybe they could press some button to get the desired
> effect, but in practice there's more involved than pressing buttons - some
> debate between humans!

Exactly. That is precisely the point I am getting at. Humans need facts so
they can debate and decide. A job number is just an indirection to the
facts. Unless it adds value, it adds cost.

> The really important thing is that the user can simply identify the job in
> question and they have no problem in doing that.

Nope, you're going to have to explain this to me. A number doesn't identify
anything. It might discriminate between things, but by itself that is not
useful.

> I suspect that if I'd
> called it a transaction number and used text there wouldn't be much of an
> argument, but in practice a simple numeric value does nicely and auto
> increment would work nicely too.

Oh, we don't want to go THERE! I seriously doubt that we share an idea of
what a transaction is and how it is properly defined. I am about to leave
on holiday and I don't have time to get into that one! :-)

> > The user can know that a job is urgent or in trouble only because
> > of the intrinsic facts about that job. The only slight advantage of a
> > shorthand synthetic is if you want to type a command, e.g. kill job 456
>
> Effectively, I'm allowing them to do that but with human interaction. If
> your comfortable with a command line interface using such a value, you
can't
> dismiss the use of these artificial keys in general.

Agreed, but we don't see too many users asking for command-line interfaces
these days. And properly so.

> >, but
> > the user would almost certainly prefer a GUI even if they had a
shorthand
> > they could use. And if they had a GUI they wouldn't need (or miss) the
> > synthetic key.
>
> They do have a GUI and they still will refer to the job number -
infinitely
> preferable to the 12th job submitted at 11:01 for the payroll run..

I need more persuading. To my eye this example looks like they're not
getting enough facts, not that they don't have a job number.

> > > So, this is a case where I'd use an artificial key that's exposed to
the
> > > user.
> > >
> > > I've no doubt I could think of more scenarios, but that's at least one
> to
> > > chew over.
> >
> > As I said in an earlier post, these synthetic identifiers have a place,
> but
> > as your last sentence makes clear, those places are more rare than you'd
> > guess from looking at the average database design. Even if the system
can
> > generate values automatically, they (usually) make more work, not less,
> and
> > they (usually) create more doubt and confusion, not less. Use them only
> > very reluctantly.
>
> I will agree with that, but I suspect that people sometimes use data that
> they consider to be immutable in keys when it is not immutable.

I don't insist that it be immutable, and nor does SQL, and nor does Ingres.
When we define a foreign key constraint, and we find that the referenced key
is mutable (because users do make typos and not all data carry a
check-digit), we should make the FK constraint ON UPDATE CASCADE. Thus when
the referenced key is updated/corrected, the correction is propagated
everywhere automatically, no matter what program was used to update the
table. (I completely agree that one would prefer not to have to update keys
in that way too often, for purely practical performance reasons, but it is
not very expensive as it turns out. And coding it is dirt cheap.)

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"


.

Quantcast