help with tree-like structure

Question

I've got some financial data to store and manipulate. Let's say I have 2 divisions, with offices in 2 cities, in 2 currencies, and 4 bank accounts. (It's actually more complex than that.) I want to show a list like this:

Electronics
    Chicago
        Dollars
            Account 2 -> transactions in acct2 in $ in chicago/electronics
        Euros
            Account 1 -> transactions in acct1 in E in chicago/electronics
            Account 3 -> etc.
            Account 4
    Brussles
        Dollars
            Account 1
        Euros
            Account 3
            Account 4
Dessert Toppings
    Chicago
        Dollars
            Account 1
            Account 4
        Euros
            Account 2
            Account 4
    Brussles
        Dollars
            Account 2
        Euros
            Account 3
            Account 4

So at each level except the top, the category can appear in multiple places. I've been reading around about the various methods, but none of the examples seem to address my particular use case, where nodes can appear in more than one place in the hierarchy. (Maybe there's a different name for this than "tree" or "hierarchy".)

I guess my hierarchy is actually something like Division > City > Currency with 'Electronics' and 'Euros' merely instances of each level, but I'm not quite sure how that helps or hurts.

A few notes: this is for a demo site, so the dataset won't be large -- ease of set-up and maintenance is more important than query efficiency. (I'm actually considering just building a data object by hand, though I'd much rather do it the right way.) Also, FWIW, we're working in php with an ms access back-end, so any libraries out there that make this easy in that environment would be helpful. (I've found a couple of implementations of the nested set pattern already.)

Answer 1

I'm not sure what database platform you're using. But if you're using MS SQL Server, then you should check out recursive queries using common table expressions (CTEs). They're easy to use and are designed for exactly the type of situation you've illustrated (a bill of materials, for instance). Check out this website for more detail: http://www.mssqltips.com/tip.asp?tip=1520

Good luck!

Answer 2

Are you sure you want to use a hierarchical design for this? To me, the hierarchy seems more a consequence of the desired output format than something intrinsic to your data structure.

And what if you have to display the data in a different order, like City > Currency > Division? Wouldn't that be very cumbersome?

You could use a plain structure instead, with a table for Branches, one for Cities, one for Currencies, and then then one Account table with Branch_ID, City_ID, and Currency_ID as foreign keys.