Re: [SQL] surrogate key or not?

[Iain]

Hi,

> But, once a surrogate key is assigned to a row, doesn't it become a
> "real" data? For example, I have a bunch of invoices/receipts and I
> write down a unique number on each of them. Doesn't the unique number
> become part of the information contained by the invoice/receipt itself
> (at least as long as I'm concerned)?

I don't think that I'd call an invoice number a surrogate key in the first
place. Invoice numbers and their like come from business requirements, they
just happen to be highly suitable as PKs so they could be considered a
natural key.

> > 3) Mutability:  Most RDBMSs are very inefficient about CASCADE deletes
and
>
> Change management IMO is perhaps the main reason of surrogate/artificial
> key. We often need a PK that _never_ needs to change (because it can be
> a royal PITA or downright impossibility to make this change; the PK
> might already be printed on a form/card/document, recorded on some
> permanent database, tattoed/embedded in someone's forehead, etc).
> Meanwhile, every other aspect of the data can change (e.g. a person can
> change his name, sex, age, email, address, even date & place of birth).
> Not to mention data entry mistakes. So it's impossible to use any
> "real"/natural key in this case.

I've never felt that it's a good idea to be dependent on the DBMS providing
cascade functionality - particularly cascading updates to PKs. I don't think
I've ever worked on a DB that used such constraints.

> Okay, so surrogate key makes it easy for stupid people to design a
> database that is prone to data duplication (because he doesn't install
> enough unique constraints to prevent this). But I don't see how a
> relation with a surrogate key is harder to "normalize" (that is, for the
> duplicates to be removed) than a relation with no key at all. Compare:

I think that most of us would agree that whatever they are, surrogate keys
aren't a substitute for clean data (or quality data).

On the whole, I think that there are more important indicators of quality
(or lack of it) in your database design than the prevalence (or lack) of
numeric ID style keys. Personally, I've grown to appreciate the id approach
over the years, but my mind is always open to other ideas.

regards
iain



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] surrogate key or not?

[Iain]

> > Because there is no information to be had on this fact. The
> > patient IS afflicted by such or she is not. There is no why.
>
> I begin to see why I spent $2000 this spring to have a doctor tell me what
I
> *didn't* have ...

This reminds me of a project I worked on many years ago, I was pretty much
fresh out of university writing a system for a large medical practice -
itwas principally for accounting purposes. I made lots of suggestions like
Josh's, only to get replies like Karsten's. I the progammer wanted to codify
everything so as to enable data analysis (ie linking complaints and
diagnosis, etc) but the doctors wern't interested. They just wanted to write
free text comments. And the reason for it (as far as I can tell) is the
distinction between general practice and reseach (such as epidemiology). So
(GPs) are not so much searching for new knowlege in their patients records,
as applying the knowlege gained from research (done by researchers) to treat
individual patients.

Karsten might beg to differ, I don't know, but the "long flaky text" comment
triggered some old (and fond) memories ;-)

Cheers
iain


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] surrogate key or not?

[sad]

hello

> This reminds me of a project I worked on many years ago, I was pretty much
> fresh out of university writing a system for a large medical practice -
> itwas principally for accounting purposes. I made lots of suggestions like
> Josh's, only to get replies like Karsten's. I the progammer wanted to
> codify everything so as to enable data analysis (ie linking complaints and
> diagnosis, etc) but the doctors wern't interested. They just wanted to
> write free text comments. And the reason for it (as far as I can tell) is
> the distinction between general practice and reseach (such as
> epidemiology). So (GPs) are not so much searching for new knowlege in their
> patients records, as applying the knowlege gained from research (done by
> researchers) to treat individual patients.

Here the situation quite similar, a customer dictate drives the practice far 
far from logic.

EVERY database i had desined in few years have been turned into a container of 
textual MEMOs completely unstructured. (because of patches and makeups)

USER DO NOT WANT TO TAKE CARE OF THEIR INPUT.

i do not know how are you all programming, really hope you are in defferent 
circumstances. Here a customer itself is a user and itself is a [man who 
formulate a problem] then every stupid idea to simplify input immediately 
turns to an urgent official order to do.

Sorry. That is my work.

Thank you all again for very usefull discussion on Surrogate Keys.



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] Trigger functions with dynamic SQL

[Andreas Haumer]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi!

I have solved the problem!

Tom Lane wrote:
> Andreas Haumer <[EMAIL PROTECTED]> writes:
>
>>I just can't figure out where and how many quotation marks
>>I have to place in my function.
>
>
> It's messy all right.  The "dollar quoting" feature in 7.5 should make
> it a lot less painful, since you can stop having to double and re-double
> quote marks.  If you're interested in using beta-quality code, you can
> have that today.  An example would go something like
>
> CREATE FUNCTION mytrigger() RETURNS trigger AS $PROC$
>   DECLARE
>   ...
>   EXECUTE $$ SELECT ... FROM $$ || tgargv[0] || $$ WHERE col = 'key' $$;
>   ...
>   END
> $PROC$ LANGUAGE plpgsql;
>
> Here I've used minimal dollar quotes ($$) for the literal constant parts
> of the EXECUTE'd query, which allows me not to have to double the quote
> marks that I actually want in the query text (the ones around "key").
> And I used dollar quotes with a label ($PROC$) at the outermost level
> to quote the entire function body, so that there's no conflict with the

Yes, this "dollar quoting" seems to make things a lot clearer!

> embedded dollar quotes.  In 7.4 the same EXECUTE command would have to
> be written
>
>   EXECUTE '' SELECT ... FROM '' || tgargv[0] || '' WHERE col = key '';
>
> which is already getting painful, and more complex cases get rapidly
> worse.  With dollar quoting you can write the constant parts of your
> query the same way you normally would.
>
As I currently use 7.4 I had to stick with lots of quotation marks,
but I finally managed to get it right and it works!

My plpgsql generic trigger function for maintaining referential
integrity with temporal tables now looks as follows:

CREATE FUNCTION func_fk_temporal_trigger () RETURNS "trigger"
AS '
DECLARE
  referer_tab text;
  referer_col text;
  referenced_tab text;
  referenced_col text;
  stmt varchar(4000);
  result record;

BEGIN
  referer_tab := TG_ARGV[0];
  referer_col := TG_ARGV[1];
  referenced_tab := TG_ARGV[2];
  referenced_col := TG_ARGV[3];

  stmt := '' SELECT id FROM '' || quote_ident(referer_tab);
  stmt := stmt || '' WHERE '' || quote_ident(referer_tab) || ''.bis = infinity 
'';
  stmt := stmt || '' AND '' || quote_ident(referer_tab) || ''.'' || 
quote_ident(referer_col) || '' IS NOT NULL'';
  stmt := stmt || '' AND NOT EXISTS (SELECT id FROM '' || quote_ident(referenced_tab);
  stmt := stmt || '' WHERE '' || quote_ident(referer_tab) || ''.'' || 
quote_ident(referer_col) || '' = '' || quote_ident(referenced_tab) || ''.'' || 
quote_ident(referenced_col);
  stmt := stmt || '' AND '' || quote_ident(referenced_tab) || ''.bis = 
infinity)'';

  FOR result IN EXECUTE stmt LOOP
RAISE EXCEPTION ''temporal table referential integrity violation - key referenced 
from %.% not found in %.%'', referer_tab, referer_col, referenced_tab, referenced_col;
  END LOOP;

  RETURN new;
END;
'
LANGUAGE plpgsql;

And these are some of the trigger definitions which use this function:

CREATE TRIGGER trigger_fk_pns_ug
AFTER INSERT OR UPDATE ON t_pns
FOR EACH ROW
EXECUTE PROCEDURE func_fk_temporal_trigger ('t_pns', 'ug', 't_ug', 'id');


CREATE TRIGGER trigger_fk_ug_pns
AFTER DELETE OR UPDATE ON t_ug
FOR EACH ROW
EXECUTE PROCEDURE func_fk_temporal_trigger ('t_pns', 'ug', 't_ug', 'id');


CREATE TRIGGER trigger_fk_pnspar_pns
AFTER INSERT OR UPDATE ON t_pnspar
FOR EACH ROW
EXECUTE PROCEDURE func_fk_temporal_trigger ('t_pnspar', 'pns', 't_pns', 'id');


CREATE TRIGGER trigger_fk_pnspar_par
AFTER DELETE OR UPDATE ON t_pnspar
FOR EACH ROW
EXECUTE PROCEDURE func_fk_temporal_trigger ('t_pnspar', 'par', 't_par', 'id');

[...]

With this single generic trigger function I can now save myself
of writing (and maintaining) literally dozends of specialized
functions. From what I can say after a few tests it seems to
work quite fine!

Note that I use "FOR result IN EXECUTE stmt LOOP" to process the
results of the dynamic query. To avoid this, I first tried to put
the whole function body including the "RAISE EXCEPTION" statement
into the dynamic query, but it seems the plsql parser doesn't like
this.

May I suggest to put this or a similar example into the
PostgreSQL manual in chapter 35 (Triggers) and/or 37.6.4
(Executing Dynamic Commands)? I'm sure this will save
other peoples time (ok, one can also use google to find
this mail in the archives... ;-)

Regards,

- - andreas

- --
Andreas Haumer | mailto:[EMAIL PROTECTED]
*x Software + Systeme  | http://www.xss.co.at/
Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0
A-1100 Vienna, Austria | Fax: +43-1-6060114-71
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFBBR1OxJmyeGcXPhERAo20AKDDv5pOi/3PMx1RhbKzQqIMv9rdOwCgvQIS
XAzqpB+j1i92ao0FHOkh/kY=
=0xfX
-END PGP SIGNATURE-


---

Re: [SQL] surrogate key or not?

[Karsten Hilbert]

> This reminds me of a project I worked on many years ago, I was pretty much
> fresh out of university writing a system for a large medical practice -
> itwas principally for accounting purposes. I made lots of suggestions like
> Josh's, only to get replies like Karsten's. I the progammer wanted to codify
> everything so as to enable data analysis (ie linking complaints and
> diagnosis, etc) but the doctors wern't interested.
Likely it wasn't their intent with the system. That doesn't
mean it can't be done ... (not that I think that with todays
tools it sufficiently can but ... :->  ICPC probably comes closest
to that). Anyway, I am highly interested in increasing the data
quality in my records. However, to be able to care for a
patient *when needed* I won't allow software I write to
*force* coding upon me. Trust me I have and am still
researching coding, classifying, structuring of medical data
at an ongoing basis which funnily constantly improves my daily
abilities as a doctor. Now, back to GnuMed, we *do* allow to
code arbitrary pieces of narrative with arbitrarily many codes
from arbitrarily many coding systems. Same for classifying
(rather typing) data.

> They just wanted to write free text comments. And the reason for
> it (as far as I can tell) is the distinction between
> general practice and reseach (such as epidemiology).
Rather it is the difference between reality and theory. In
reality you are dealing with tens of patients with 1-5
problems almost neither of which are "quite right" if you go
by the textbooks. Nevertheless everyone expects you'll never
forget/do wrong a thing.

> So (GPs) are not so much searching for new knowlege in their patients records,
> as applying the knowlege gained from research (done by researchers) to treat
> individual patients.
One thing we ARE looking for in our records is the ability to
find groups of patients by arbitrary criteria since one day
I'll have to find all my patients whose father took a statine,
whose second-born child suffered a bout of neutropenia 2 weeks
after birth and who started being on the pill at age 14.
Because they'll have a 3fold increased risk of lung embolus.
Unless monitored for clotting factors every 6 months. Which I
will have to do from now on. Get my point ?  :-)

> Karsten might beg to differ, I don't know, but the "long flaky text" comment
> triggered some old (and fond) memories ;-)
I totally understand what you are saying... We do cling to old
trusted things. Also, I used a bit of hyperbole to get my
point across.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])