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> 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.