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@xxxxxxxxxxxxxxx>
Rob Marjot wrote: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.
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...
There certainly are some cases where an EAV solution is the proper one, and yours may be one of them. But most aren't.
