John Small wrote:
Basically I'm storing heterogenius, hierarchical data and ultimately
it has to be flattened. I'm flattening it outside the database now.
What I really need is a deductive/associative database or a multidimensional
database but the customer wants it on the cheap. Sorry I can't give
you more details.
Hierarchical data?
So perhaps something like:
id, parent_id, data
Example data:
title1
|- title1A
|- title1B
| |- title1B1
| |- title1B2
|- title1C
title2
Data in database:
-----------------------------
| id | parent_id | data |
|---------------------------|
| 1 | 0 | title1 |
| 2 | 0 | title2 |
| 3 | 1 | title1A |
| 4 | 1 | title1B |
| 5 | 1 | title1C |
| 6 | 4 | title1B1 |
| 7 | 4 | title1B2 |
-----------------------------
The above example is good for some cases and bad for others. Of course
one solution could be also to save the whole path to the column, for
example in text-column, using indexes separated by commas.
-----------------------------
| id | parent_id | data |
|---------------------------|
| 1 | 0 | title1 |
| 2 | 0 | title2 |
| 3 | 1 | title1A |
| 4 | 1 | title1B |
| 5 | 1 | title1C |
| 6 | 1,4 | title1B1 |
| 7 | 1,4 | title1B2 |
-----------------------------
But again, this has it flaws. For example updating the hierarchy needs
much more work than the first example needed. But then again, it is much
easier to find all the parents for certain leaf.
The example given in here is simplified example. This can be used as it
is, but "proper" way would propably be to add another table, where you
save all the parents and their order for certain child.
So something like:
leaf_id | parent_id | order
---------------------------
3 | 1 | 1
4 | 1 | 1
5 | 1 | 1
6 | 1 | 1
6 | 4 | 2
7 | 1 | 1
7 | 4 | 2
The above contains the same information as previous, but this time the
data is separated to rows, as it should be in databases. From this table
you can easily search all parents for certain leaf,
select parent_id from table where leaf_id=6;
but you can also search all children and grant children etc. for certain
parent.
select leaf_id from table where parent_id=1;
I bulieve there are other solutions also, it greatly depends on the
details of the problem which is best to use.
