RE: [Info-ingres] Re: is there an equivavlent to auto

RE: [Info-ingres] Re: is there an equivavlent to auto_increment i n ingres ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul White - 20 Jul 2005 02:04 GMT

The advantage of using the unique system maintained key is so you can
differentiate between otherwise duplicate records. Very useful if you have
thousands of rows being inserted and you dont want to go off to a secondary
table to increment a unique number for every row.

I've seen some pretty ugly (is that grammatically correct?) attempts to deal
with this problem.

-- Timestamp, put a sleep(1)in between each insert. You also need user_id,
some unique session id in the key.
-- tid (aaaaaarrrrgggghhhhhh!!!)
-- Read all the records into an array, assign a number, delete the records,
re-insert one by one.
-- pseudo random generator controlled by the client.

By the way, here is an example:

create table users (
userid table_key with system_maintained,
username varchar(100) ) ;

insert into blah (username) values ('fred');
insert into blah (username) values ('mary');
insert into blah (username) values ('paul');

select * from blah;

userid username
\001\000\000\000\000\000\000\000 fred
\002\000\000\000\000\000\000\000 mary
\003\000\000\000\000\000\000\000 paul

help blah;
Name: blah
Owner: ingres
Created: 29/03/05 14:12:49
Type: user table
Version: II2.6

Column Information:
Key
Column Name Type Length Nulls Defaults Seq
userid TblKey,SM no value
username varchar 100 yes null

table_key Ingres maintains a unique identifier for the table.
object_key Ingres maintains the unique identifier for the database.

The manual needs a bit of work in this area - I have an issue open on this.
Check out sqlref.pdf. search for "table_key" and "system maintained". Also
look at some of the correspondence on the the newsgroup
comp.databases.ingres.

There are limitations to copying data to / from these tables (as in other
dbms) and you shouldnt reference the fields in other tables.

Paul

Reply to this Message

Roy Hann - 20 Jul 2005 09:13 GMT

> The advantage of using the unique system maintained key is so you can
> differentiate between otherwise duplicate records.

So you make duplication of facts undetectable by adding a spurious and
trivially unique value. That solves the problem...how?

> Very useful if you have
> thousands of rows being inserted and you dont want to go off to a secondary
> table to increment a unique number for every row.

Definitely, but a slightly less bad way of doing a bad thing is not a good
solution.

> I've seen some pretty ugly (is that grammatically correct?) attempts to deal
> with this problem.
[quoted text clipped - 5 lines]
> re-insert one by one.
> -- pseudo random generator controlled by the client.

All vile, I agree.

> By the way, here is an example:
>
[quoted text clipped - 5 lines]
> insert into blah (username) values ('mary');
> insert into blah (username) values ('paul');

I realize this was probably just a quick-and-dirty example to illustrate the
use of system maintained keys, but it will serve equally well to illustrate
the preferred solution:

create table users (
username varchar(100) unique ) ;

This is better for at least seven reasons: (1) it explicitly represents a
meta-fact about the business model (that is, username is intended to be
unique *and identifying*). (2) It allows the DBMS to enforce a contract on
all applications; it tells the users how the data can legitmately be used,
and it tells the programmers exactly how their applications must behave.
(3) it prevents the correctly-framed query "SELECT * FROM users WHERE
username='fred' from ever returning an incorrect result. (4) it eliminates
the need to design, code, test, debug, and document application functions
for discovering, preventing, and fixing duplications. (5) it avoids using
Ingres' proprietary and badly broken system maintained logical key types.
(6) it liberates the end-users so they can use any application they like,
not just those containing the necessary boiler-plate code mentioned in point
4 above. And (7), it eliminates the need to join referencing tables (that
have userid as a foreign key) with the users table to discover the username.

IMO, point 3 above is the clincher.

System maintained "keys" just reintroduce the pointers that relational DBMSs
were intended to banish. (They weren't left out by accident.)

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

Reply to this Message

Paul Andrews - 20 Jul 2005 09:57 GMT

I thought I'd jump in here and add in a scenarios where <sharp intake of
breath> I don't have a problem with artificial keys.

For example, a queue processor where jobs are added to a queue and users can
view the queue to see the progress of their job. Of course, we could
timestamp the job and locate it with the userid and timestamp and maybe the
jobname, but users would find it infinitely more convenient to refer to job
456 on the queue and be able to say that job 456 appears to have a problem,
rather than saying the job I submitted at 10 O'clock needs to be rushed
through. it's far more convenient to key the job queue on the job number and
have the system increment the jobnumber as jobs are submitted.

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.

Paul

Reply to this Message

Roy Hann - 20 Jul 2005 10:25 GMT

> I thought I'd jump in here and add in a scenarios where <sharp intake of
> breath> I don't have a problem with artificial keys.

They are not invariably wrong, bad, or even undesirable. They just usually
are.

> For example, a queue processor where jobs are added to a queue and users can
> view the queue to see the progress of their job. Of course, we could
[quoted text clipped - 4 lines]
> through. it's far more convenient to key the job queue on the job number and
> have the system increment the jobnumber as jobs are submitted.

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

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

Roy

Reply to this Message

Paul Andrews - 20 Jul 2005 11:25 GMT

> > I thought I'd jump in here and add in a scenarios where <sharp intake of
> > breath> I don't have a problem with artificial keys.
[quoted text clipped - 22 lines]
> 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.

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!

The really important thing is that the user can simply identify the job in
question and they have no problem in doing that. 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.

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

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

> > So, this is a case where I'd use an artificial key that's exposed to the
> > user.
[quoted text clipped - 8 lines]
> 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.

Paul

> Roy
>
> _______________________________________________
> Info-ingres mailing list
> Info-ingres@cariboulake.com
> http://mailman.cariboulake.com/mailman/listinfo.py/info-ingres

Reply to this Message

Roy Hann - 20 Jul 2005 12:54 GMT

> > I don't want to pick an endless stream of specific examples apart, so I
> will
[quoted text clipped - 10 lines]
> 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
[quoted text clipped - 3 lines]
> 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
[quoted text clipped - 3 lines]
> 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.
[quoted text clipped - 14 lines]
> 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"

Reply to this Message

Paul Andrews - 20 Jul 2005 13:25 GMT

> > > I don't want to pick an endless stream of specific examples apart, so I
> > will
[quoted text clipped - 25 lines]
> they can debate and decide. A job number is just an indirection to the
> facts. Unless it adds value, it adds cost.

The humans only need the job number to find the other facts. The use of the
job number simplifies lookup by humans and by query - reducing 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.

Lets suppose they submit 25 jobs. Each job has 5 parameters. The first
parameter might be department, the second might be the job type, the third
might be a start date, the fourth an end date, etc.

If they have an enquiry about a given job (for example it's run but nothing
came out of the printer) it's far easier to say that "I have a problem with
job 486", rather than say "I have a problem with my job for department A,
type payroll, with a start date of 1/4/05 that was submitted at about
11:01 - ignore the same job that was put in by by colleague at almost the
same time".

In pure convenience terms, these jobs sometimes have related tables holding
extra parameters and rather than hold a complicated foreign key refering to
the job queue , they just refer to the job number in the queue that they
relate to.

Does that help?

> > I suspect that if I'd
> > called it a transaction number and used text there wouldn't be much of an
[quoted text clipped - 4 lines]
> 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! :-)

I didn't have SQL transactions in mind - a tracking number for example.

> > > 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
[quoted text clipped - 61 lines]
> Info-ingres@cariboulake.com
> http://mailman.cariboulake.com/mailman/listinfo.py/info-ingres

Reply to this Message

Roy Hann - 20 Jul 2005 14:31 GMT

> > > 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 -
[quoted text clipped - 8 lines]
> The humans only need the job number to find the other facts. The use of the
> job number simplifies lookup by humans and by query - reducing cost..

I'm about to leave for sunnier climes so I am simply going to toss the towel
in here. One can discover examples where synthetic keys are truly useful,
and I think we've agreed on that all along. My purpose in arguing about
this has been to plant a seed of doubt that synthetic keys (and surrogate
keys) are as useful as (I think) we generally assume. They're not.

Roy

Reply to this Message

Paul Andrews - 20 Jul 2005 14:50 GMT

> > > > In an ideal world maybe they could press some button to get the
> desired
[quoted text clipped - 19 lines]
>
> Roy

OK, have a great holiday!

Paul

Reply to this Message

--CELKO-- - 20 Jul 2005 11:01 GMT

Isn' t queuing an example of --shudder-- procedure and not data?

One of the basic rules is to model the elements and structures in
sepratre tables. I'd have a table for the queue elements (people in a
butcher shop) and another for the queue tickets (00 to 99) associated
with them. For computer jobs, we had all kinds of rules about queue
priority, such as "Student jobs come in with priority 1000+", "Teacher
jobs come in with prioirity 100+" and "Payroll comes in with prioirity
10+" and servicing the queue "decrement prioirity numbers by 1 every 2
minutes" and "service as many jobs with the lowest prioirity numbers as
will fit into main storage".

Reply to this Message

Paul Andrews - 20 Jul 2005 11:31 GMT

> Isn' t queuing an example of --shudder-- procedure and not data?
>
[quoted text clipped - 7 lines]
> minutes" and "service as many jobs with the lowest prioirity numbers as
> will fit into main storage".

I'm not sure how your example affects the requirement to easily identify
individual jobs to a user and hold the information in one or more tables.

On the system I have in mind we have several tables involved. The queue
table is effectively holding the queue tickets and does have priority rules,
etc.
If I hadn't suggested a queue number and said transactionid, I doubt an
eyebrow would be raised. In my case a simplistic transaction id (job number)
is using an integer, automatically incremented as jobs are added.

Paul

> _______________________________________________
> Info-ingres mailing list
> Info-ingres@cariboulake.com
> http://mailman.cariboulake.com/mailman/listinfo.py/info-ingres

Reply to this Message