Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic properties/attributes of the entity Expand / Collapse
Author
Message
Posted Friday, August 02, 2013 8:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 02, 2013 8:10 AM
Points: 3, Visits: 1
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.



Post #1480416
Posted Saturday, August 17, 2013 11:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1485554
Posted Sunday, August 18, 2013 4:50 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:44 PM
Points: 8,286, Visits: 8,736
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
Post #1485625
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse