Re: Help request to improve function performance

On Wed, Apr 22, 2009 at 06:21:41PM -0600, Scott Marlowe wrote:

> > CREATE TABLE "app"."archetype_data" (
> >   "id" BIGINT NOT NULL,
> >   "context_id" VARCHAR(1000),
> >   "archetype_name" VARCHAR(1000),
> >   "archetype_path" VARCHAR(1000),
> >   "name" VARCHAR(1000),
> >   "value_string" VARCHAR(1000),
> >   "value_int" BIGINT,
> >   "value_double" DOUBLE PRECISION,
> >   "session_id" VARCHAR(1000),
> >   "instance_index" INTEGER,
> >   CONSTRAINT "archetype_data_pkey" PRIMARY KEY("id")
> > ) WITHOUT OIDS;
>
> If I'm not mistaken, you're doing Entity Attribute Value model type
> storage. I.e. a database in a database. Makes for easy coding, and
> danged near impossible to troubleshoot your data.

The upper layer model he's trying to store data of has
extremely well modelled constraints complete with
verification engine and all. So while your concern is valid
it's probably less so.

> It's a religious issue but I come down on the side that good data
> modelling is hard for a reason, because it pays you back so much in
> the end.
The hard part has already been done for him in a very
thoughtful way: They've got a model, a metamodel and yet
another model for data instances of the model ;-)

The design group of the above methodology pretty much
suggested not putting too much additional modelling into the
data store (not that I agree too much)

@Seref: Thomas Beale said so ;-)

I dare say the extension power of PostgreSQL would lend
itself extremely well to actual implementation of the
OpenEHR model right in the database (mapping OpenEHR types
to complex types including real-time verification, building
AQL right into PostgreSQL in the form of stored procedures,
etc) but it would take some serious effort.

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