Normalised data.

[borntorun]

Hi Guys,

I have a table a dimension table with the following data heirarchy:

ID
Name
ParentID
Level

THe problem is for reporting in one query they want the user to be able to select one name and bring back all the data above and below it. Doesn't really work.

Any idea how i can change the database to make this easier.

Thanks man.

 

[borntorun]

Apologies if it didn't seem clear.

Maybe not enough information i hope this helps someone to come up with ideas:

Heirarchy:

Table desc from memory as they are my customer/client.

ID INTEGER
Name VARCHAR
ParentID INTEGER
Level VARCHAR.

For example sample data

7898 GeorgeBush p0909 1
p0900 Texan f8909 2
f8909 America bghrh 3

Upto six levels

Every record has just one parent but can have multiple children.

The problem is i can't think how to create a view or query where by the user can select say Texan and bring back all the rows at levels below and above. They don't want to limit just on the bottom level they might limit say at level 4 for on something like republican and then next time at another level and they want this to be dynamic.

I hope this makes more sense if not let me know.

Thanks for any pointers.

 

[Sridharan]

borntorun,

It is very clear except for the fact that you haven't mentioned which DB are you on. If you are on Oracle there is a CONNECT BY Clause which will help one to navigate a table in a tree hierarchy which is what you are looking at. Are you on Oracle???

Lemme know

Sri

 

[borntorun]

Yes this is on Oracle...

We are looking at the connect by now.

Thank you for your time.

 

[johnherman]

If you are not ORacle, see Ralph Kimball article.

<

http://www.dbmsmag.com/9809d05.html>

Sometimes the grass is greener on the other side because there is more manure there - original.