August 2, 2013 at 8:10 am
Hello,
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:
Nodes
( NodeId,
Father_NodeId, -- references Node( NodeId ),
Short_Description1,
Long_Description2,
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_Atrribute_Of_Final_Node
( Node_Id,
Start_Date,
End_Date,
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.
Allowed_Attributes
( Attribute_Id,
Attribute_Name
Type_Of_Value, -- short string, long string, BLOB, short number, long
-- number )
EAV_Of_Final_Node
( Node_Id,
Attribude_Id,
Starting_Date,
Ending_Date
Attribute_Value_Short_string,
Attrbute_Value_Long_String,
Attribute_value_short_number,
...
Attribute_Value_BLOB,...
... )
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.
August 17, 2013 at 11:10 pm
I'm thinking that this is way too big a question for a forum.
So far as your point-in-time stuff goes, I believe you're headed in the right direction.
I think your biggest problem will be the maintenance of the data. What are you using to guarantee that your point-in-time data is accurate according to the Type II Slowing Changing Dimension that it appears that you're using?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2013 at 4:50 pm
It may be useful, given that you have temporal data, to look at (Dace's version, not Fagin's) of 6NF and some of the work on Anchor modelling (and Knot and Attribute tables). I'm not sure how this combines with an EAV approach at the leaf level of a hierarchy, but it's awfully easy to get temporal data screwed up either from a performance point of view or from an integrity point of view (or indeed both) if one starts from scratch rather than building on what's already been done, and I suspect that the effort of making it fit with EAV (even though that certainly means you won't really be 6NF) may be less than the effort of inventing a replacement for what other people done so far. I could of course be horribly wrong there - 6NF is generally outside of my experience, I certainly haven't combined it with EAV, and I haven't read anything that indicates that anyone else has, either, so ....
Just a suggestion of something you might find it useful to look at, not a suggestion that it's anything you should definitely do.
Tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply