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

Thank you, Ben. Well, I'm afraid you got the basic idea... I intend to
implement a hybrid between a fixed schema and an Entity-Attribute-Value
scheme. The schema will be able to cover 90% of the data needs; in other
cases (specific projects) additional fields (and/or tables/relations) will
be needed; including their constraints...
I'm experienting now with some smart thought that just came up: passing a
set of key/value pairs to function that will test the new row; on insert /
update the following could then be checked (as part of a RULE-set):

SELECT doesComply('relationname', keyValues.*) FROM (VALUES('col1',
CAST(col1 AS TEXT)), VALUES('col2', CAST(col2 AS TEXT))) AS
keyValues(the_key, the_value);

The function "doesComply()" will then process the CONSTRAINTS table and
raise an Error if the new / updated row does not fit...

Any thoughts?

Rob

2009/9/24 Ben Chobot <bench(at)silentmedia(dot)com>

> InterRob wrote:
>
>> Dear List,
>>
>> I am trying to implement the following:
>>
>> [snip]
>>
>> All suggestions are very much appreciated,
>> regards,
>>
>>
>> Rob
>>
>>
>>
> It's not clear to me what you're asking, but I suspect the suggestion you
> need is the same as if you had asked how to best implement an
> Entity-Attribute-Value scheme: don't do it. Why it may be possible,
> performance is going to go into the toilet, constraints are going to be
> difficult to enforce, and maintenance will be difficult at best. Spending
> the effort upfront to define a schema will have drastic long-term payoffs.
> It can be tempting to believe an application can define the appropriate
> schema for itself at runtime if you just give it a big enough sandbox, but
> this rarely works out well.
>
>