Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rpoojari
Starting Member
6 Posts |
 Posted - 2008-10-16 : 08:00:39
|
| Hi, I am working on hierarchical data maintenance application, where the data is made up of nodes and attributes. data is displayed in a tree structure and each node has list of attributes. each child node inherits the attributes from the parent by default with some exceptions. users can add new nodes/update node attributes/move nodes in this tree and it goes through workflow for approval. once the changes are approved the changes are committed to the live tree. I created a generic data model using node and attribute tables with typeids to differentiate them. I have also created nodeaudit and attributeaudit tables to separate history for performance reasons, but should I keep pending changes and the live data in separate tables OR use one table with a flag to indicate if it is pending or live? which option is better? a node or attribute can only have one pending version at any time. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-16 : 08:37:01
|
| For performance generally smaller will be better but it all depends.In this case on the surface at least it sounds like it might make more sense to split them out. Depending on your scenario it can be difficult or harder to enforce RI when you have a current, pending and multiple historical PKs in a single set of tables. Its really hard to tell though. Can you have a mix of pending and current nodes or does the whole graph have to be in the same state, i.e. can you have a pending node with active children or vice verca? Can you elaborate on the situation at hand. |
 |
|
|
rpoojari
Starting Member
6 Posts |
Posted - 2008-10-16 : 09:32:50
|
| it is possible for a pending node to have active children only when user creates a node and then moves active children from other part of the tree under this node. but nodes cannot jump between levels. even after move, the new level/depth of the node should be same. from T-sql point of view, I found it easier to keep pending and live data in one table and then query one table to get pending data if any for set of nodes or get live data if no pending data exists. also whenever there is a pending record for a node, I mark the live the record with a flag to indicate that it has a pending change. this will help me in avoiding a self join to check if any pending data exists. the advantage of keeping pending and live data in one table is that I can fetch the data without using any joins and use some condition in where clause to filter data accordingly. I use a separate table to generate nodeid using identity column and then use that nodeid in nodeversion table (as foreign key) which holds the pending as well as live records for the node. but some people suggested me that it is better design to separate the pending and live data into separate tables. but that makes querying complex, I have to join two tables and then use series of case statements for every column to return pending data if exists, otherwise return live record for a node. it makes this querying even harder, if there is newly created pending node and no live record exists for that node. I need to keep a unique nodeid across pending, live/active and history tables.hope it is clear, if not, pls let me know. |
|
|
|
|
|
|
|
|
hierarchical data maintenance with workflow