SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Thursday, May 11, 2006

nested tree, how to

SQL Apprentice Question
I've problems representing nested tree in sql server strucutre ...
my scenario is :
several process (p1,p2,p3,etc..) makes several operations
i need to store the combination process,operation,time and this ismy
structure (processid,opid,dateop)
now i need to show a tree hystory of the operations in a definited date
here's a sample:
50% makes op2
20% makes op1
60% makes op3
100% makes op4
20% makes op5

50% makes op3
100% makes op6
100% makes op1

the real problem is the amount of data ., about 20 000 000 records ...
how can represent this tree in an efficient way ?!??!!?

Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. What you posted makes no sense.

>> Several process (p1,p2,p3,etc..) makes several operations (op1,op2,op3,...) I need to store the combination (process, operation, time) <<

You have described a matrix (ops by processes), not a tree. In a tree,
one process might have one or more operations as subordinates.

You show a cycle where OP1 is one of its own subordinates. Trees do
not cycles.

Have you gotten a copy of TREES & HIERARCHIES IN SQL?

No comments: