2

I need to map a calculation tree in a database.

Let's say I have these 10 entities:

id, name,                          unit
1,  "Household",                   "USD"
2,  "Household income",            "USD"
3,  "Household expences",          "USD"
4,  "Mother income job",           "USD"
5,  "Father income",               "USD"
6,  "Father income job",           "USD"
7,  "Father income investment",    "USD"
8,  "Father investment portfolio", "USD"
9,  "Father investment yield",     "Percentage"
10, "Income from job",             "USD"

A rule is that each entity is either a leaf level or has exactly two children.

That is:

  • Each parent has exactly 2 children.
  • Each child has 1 to many parents.
  • Each entity has zero or 2 children.
  • Each entity has zero, 1 or many parents.

If an entity is not on the leaf level (it has two children) it will have an operator (plus, minus, multiply or divide).

What I need is:

1. The data structure.

2. A query that lists all direct parents to an entity.

For "Father income job" the result should be:

"Father income"
"Income from job"

3. A query that output the below tree view for an entity.

For "Household" the result should be:

level, name,                          unit,         operator
0,     "Household",                   "USD",        "Minus"
1,     "Household income",            "USD",        "Plus"
2,     "Mother income job",           "USD",        NULL
2,     "Father income",               "USD",        "Plus"
3,     "Father income job",           "USD",        NULL
3,     "Father income investment",    "USD",        "Multiply"
4,     "Father investment portfolio", "USD",        NULL
4,     "Father investment yield",     "Percentage", NULL
1,     "Household expences",          "USD",        NULL

For "Father income investment" the result should be:

level, name,                          unit,         operator
0,     "Father income investment",    "USD",        "Multiply"
1,     "Father investment portfolio", "USD",        NULL
1,     "Father investment yield",     "Percentage", NULL

For "Income form job" the result should be:

level, name,                          unit,         operator
0,     "Income form job",             "USD",        "Plus"
1,     "Mother income job",           "USD",        NULL
1,     "Father income job",           "USD",        NULL

Ekstra info.

Database: SQL Server 2005.

There is an external system that provides the values for the entities - but that will be added in later, so this is outside the scope of this question.

The rules of the calculation tree are inherited and cannot be changed.

The real use case is a complex system with more than 500 entities.

Since minus and divide is possible, the order of the children is important.

I can handle the correct insertion of data from outside the database. That is, no need to make constraints or checks that ensure, say, two children per parents.

Martin Carlsson
  • 461
  • 2
  • 6
  • 18
  • Relational databases are set-based, not trees or graphs. I think you'd be much better off with a graph database. – duffymo Aug 27 '16 at 15:35
  • @duffymo thanks, but I only have access to an SQL Server 2005 :/ – Martin Carlsson Aug 27 '16 at 15:56
  • Trees are recursive. You need a table with primary key (of course) and a foreign key that refers to itself. As for rules, they should be expressed as constraints if that's possible. I think you'll find this implementation to be difficult, complex, and hard to maintain. – duffymo Aug 27 '16 at 16:19
  • Maybe this answer can help you: https://stackoverflow.com/questions/935098/database-structure-for-tree-data-structure – duffymo Aug 27 '16 at 16:24

0 Answers0