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

Hi Rob,

> In a database I wish to implement a GENERIC datamodel, thus on a
> meta-level. All RELATIONS (part of a MODEL) will be a view on some base
> (being a table) JOINed with (an) extra column(s). Thus, this view
> consists of a number of FIELDS. I whish to make this view editable
> (INSERT, UPDATE) using the RULE system. Some constraints will apply;
> enforcing these is the problem I am trying to solve by modeling these
> rules with a table "RELATION_CONSTRAINTS" (see below).

Although many people will shoot it down, I follow a very similar approach.
Data-driven application framework with exactly what you have described.
In short, my application will read the "Metadata" at runtime and "build"
the application so to speak on the fly.

For this I use the following two table approach:

meta_master:
master_no SERIAL NOT NULL PRIMARY KEY,
master_type VARCHAR(30) NOT NULL REFERENCE master_type(master_type),
master_id VARCHAR(30) NOT NULL,
master_property TEXT,
UNIQUE (master_type, master_id)

meta_link:
link_no SERIAL NOT NULL PRIMARY KEY
master_no REFERENCE meta_master(master_no),
link_type NOT NULL REFERENCE master_type(master_type),
member_no NOT NULL REFERENCE meta_master(master_no),
member_property TEXT,
UNIQUE (master_no, member_no)

Some explanation:
master_type and link_type have values like database, table, column etc.

Thus, at runtime a method FormLoad(nID) will make use of a recursive query
to load everything that is needed to build the "Form" at runtime and
associate it with the correct database, table, etc.

It is necessary to recurse all members via meta_master.master_no =
meta_link.master_no and meta_link.member_no = meta_master.master_no (use
connect_by() pre 8.4 or use the RECURSIVE views from 8.4)

Where applicable the %_property columns are used to define additional
information in the format 'name=value;nextname=value;' etc.

> I was thinking of implementing this using a FUNCTION that takes a
> polymorphic record parameter (and the relation name); then checking this
> record against the applicable constraint expression.
> This whole idea may sound like a DBMS-in-a-DBMS kind of thing... What I
> am trying is to write as little as table/view-specific code as would be
> necessary, while still collecting all base data in one central table...

I take the %_property column even further, in my business data I have a
property column again in tables where additional columns can be defined on
the fly based on the metadata, until such a time that users have a clear
picture of what they exactly need. This is also used in tables where
multi-company differences makes it almost impossible to have not more than
50% of a table's columns redundant.

If you need more info regarding this approach, feel free to contact me in
private.

Johan Nel
Pretoria, South Africa.