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