Re: pervasiveness of surrogate (also called synthetic) keys

On Fri, Apr 29, 2011 at 10:14:07AM -0500, Merlin Moncure wrote:

> I took a quick look at the gnumed schema and found it to be generally
> very thorough and excellent. If you're going to use surrogate keys,
> that's they way to do it.

Good to know since I'm only a lowly medical doctor not
having much schooling in database matters beyond this list,
the PostgreSQL docs, and the Celko book.

> That's a neat trick btw to use inheritance
> for the auditing feature...how is it working out for you?

That works very nicely for us. Same thing with aggregating
clinical narrative across diverse tables.

> Any general comments on postgresql with regards to your product?

We have found it to be very dependable and professionally
maintained. We've never lost any patient data due to crashes
(for what that's worth). The breadth of constraints one can
define saved our behinds several times by preventing buggy
applications from storing faulty patient data.

One thing that'd be helpful to have would be ON CONNECT
triggers - that would make it much safer to support HIPAA
requirements (I'm aware of the apparent fallacy of a faulty
ON CONNECT trigger preventing superuser access - that can be
overcome by not running ON CONNECT triggers in single-user
rescue mode).

Another would be database wide asserts on data. Of course,
better support of inheritance in terms of definably
propagating constraints and triggers would be nice :-)

But that's a lot to ask.

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346