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 <be...@silentmedia.com>
> Rob Marjot wrote:
>
>> 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...
>>
>
> If you absolutely must have a dynamic schema like this, and can't have a
> DBA simply add tables as needed, then I think it would be less work,
> overall, to create a schema that your application has DDL rights to, and
> then let it create and modify normal tables with normal constraints there.
>
> There certainly are some cases where an EAV solution is the proper one, and
> yours may be one of them. But most aren't.
>
>
