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

On Thu, Sep 24, 2009 at 11:07:31PM +0200, InterRob wrote:
> What I'm trying to do is to facilitate different fieldwork methodologies for
> archaeological research (on project basis); there is no final agreement on
> data structure and semantics; however, on a meta-level all choices are
> rational and can be modelled... Infact, all models can be related to each
> other: that's where the "hybrid" part comes in: I wish to implement the
> common denominator (90%) and then further extend this, enabing specific data
> model implementations -- including checks for data integrity.

I'm my experience it depends on how technically competent your users
are.  Most of mine are fine working with "their own" data files/formats
and only want the data in the database to keep track of the larger
structural stuff.

I therefore tend to just leave their data as opaque blobs (stored in
large objects, as they're reasonably small) of data and only pull out
the parts of it that are needed to keep the other parts of the projects
happy.  That way I can make sure the bits the database takes care of can
be appropriately designed and the users get to keep their data exactly as
they want.

To support this I've written various bits of code that get automatically
run when users insert their data files to pull them out into the
appropriate tables.  The detailed bits of the structure are of course
missed, but most of the time this data isn't needed and when it is they
want the rest of the original (normally proprietary binary file formats
that I've had to reverse engineer) file so that their program can figure
out what's going on.

It all depends on the problem domain of course, but this seems to work
OK for us!  I really want to hack Samba around so that the users can
view the files directly from inside the database, but I'm not sure how
good an idea this really.

The bigger datasets (1GB+) tend to be nicely structured, so they get
handled specially.

> As soon as that works, it becomes possible to record changes at row-level --
> providing access to data-snapshots in time.

I think these are what my blobs are...

> Furthermore, it becomes possible
> to build upon this central database automated tools for management and
> filing of information and different modes of data entry (including
> webbased)...

...and this is what I'd call my structural bits.

> The thing is: altering table structures (changes should be limited to adding
> columns) is required on a ad hoc basis and End User should be able to do
> so...

I generally find it's easier if I'm involved in that.  Maybe it's just
my users!

> I guess that requires some over engineering... ?

By "over engineering" I was meaning that you seem to be trying to solve
a more complicated problem than is necessary.  There will be some
essential complexity inherent in any problem, but it's the job of every
engineer (software or hardware) to ensure that only minimal amounts of
incidental complexity are introduced.


In my case the "important" thing is to make sure that we know the
state of what's going on in the projects.  I can do this by getting a
combination of data from the user (through traditional means) and by
pulling apart their data files.  The "incidental complexity" I've added,
that of writing fiddly little programs to interpret their files, seems
to be better than getting the users to input the data twice; once in
their programs and once into the database.

In your case you've introduced this strange new EAV style design and the
constraint system on top of it.  The benefits of this design may well be
better than the costs of developing it, but I have a feeling it may be
easier to "side-step" the problem somehow.

That all got a bit longer than I was expecting, but I hope it's useful!

--
  Sam  http://samason.me.uk/