Preparing the database model for application that would serve as tool for
designing the hierachical tree structures. Nodes of the trees are stored in the table like:
Father_NodeId, -- references Node( NodeId ),
NodeType, -- F - final terminal node, B - branch intermediate node
TreeId, -- identification of the tree -
-- various types of the tree in one table )
Final Terminal Nodes have Fixed_attrbibutes for which changes are
maintained in time period. For fixed attrbibute we know in advance
data type and name.
Fix_Attrib1 number( 14,2 ),
Fix_Atrrib2 varchar2( 40 ),
The main question and problems are set of attributes of final nodes
whose change values are also maintained time but who value and name
can not be predicted but are implemented as EAV mdelling technique.
Type_Of_Value, -- short string, long string, BLOB, short number, long
-- number )
My dilemma and question is how to approach to modelling and designing
the values of attributes in the table so having the optimal data structures
for validation when entering values on the form or presenting in the reports?
Second dilemma is that there are scenarious when Attrbibute value should
be some value from the referenced table.
So the Attribute type suppose to be name of the referenced table,
attribute value suppose to be the value from that table.
For the beginning let us limit that referenced tables are heving unique keys
consisted from three fields/columns of varchar data type.
Considering this is featured with tools some ERP have incorporated like
Oracle Application using Flex Field.
But this is much less formed project...
Apreciate on any direction or reference in modelling theory as experiences.