Re: generic modelling of data models; enforcing constraints dynamically...

At first glance: brilliant! I was about to implement this key/value thing with an XML type... I will take a closer look at this, thanks a lot, Oleg!

Tips & tricks to get this going in PostgreSQL?

Rob

2009/9/28 Oleg Bartunov <oleg@sai.msu.su>

Have you considered contrib/hstore to build flexible database scheme ?

Oleg

On Sun, 27 Sep 2009, InterRob wrote:

Dear David, dear Peter, dear all,
Peter, I was happy reading your reply right after I opened and read Davids.
I do think I am on the right track; it is not a matter of building the
one-and-only right schema, not in this case. Archaeology has the same twist
as has ethnography, antropology and alike: they work with (what I would
call) "narratives" (in fact, in the case of archaeology this seems to me to
be an archaeologists monologue...). They try to support their findings with
statistics and other means of quatification -- as does this modern,
rationalist world require them to do, to be taken seriously as science... I
seek to implement all this in a hybrid form; a fusion between the relational
and EAV concept.

Peter, may I invite you to privately share some more details on the system
you are using and the design of it? Did you implement it using PostgreSQL?
Looking forward to your reply.
(And with respect to your previous message: whom are you actually referring
to by the acronym "OPs"?)

Cheerz,


Rob

2009/9/27 Peter Hunsberger <peter.hunsberger@gmail.com>

On Sun, Sep 27, 2009 at 2:22 PM, David Fetter <david@fetter.org> wrote:

On Sun, Sep 27, 2009 at 08:26:27PM +0200, InterRob wrote:

Dear David, dear all,
I very well understand what you are saying...

Clearly you do not.  What you are proposing has been tried many, many
times before, and universally fails.

I've been refraining from jumping on this due to time constraints, but
this statement is silly.  We have a system that does almost exactly
what the OP wants although the implementation is slightly different:
we use an EAV like model with strong typing and build set / subset
forests to maintain arbitrary hierarchies of relationships.  Our
reasons for doing this are similar to the OPs; it's for research (in
our case medical research).  We maintain over 200,000 pieces of end
user generated metadata, describing what would be in a conventional
relational model over 20,000 columns and some 1,000s of tables but the
actual physical model is some 40 tables.   Yes, the flip side is, such
a system won't support more than 1,000,000s of transactions per day,
but that's not why you build them.

That your people are failing to get together and agree to a data model
is not a reason for you to prop up their failure with a technological
"fix" that you know from the outset can't be made to work.

Spoken like someone who has always had the luxury of working in areas
with well defined problem domains...   I can't tell you the number of
people that told us exactly the same thing when we started on it.
That was 8 years ago.  Not only can such systems be built, they can be
made to scale reasonably well.  You do need to understand what you are
doing and why: the costs can be high, but when it comes to research,
the benefits can far outweigh the costs.

--
Peter Hunsberger

       Regards,
               Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83