Database tree structure functionality

I have a database that calculates costs of a company using a managerial accountancy method (activity based costing).
The database uses 6 tables with information about the departments (cost centers), activity of the departments (process steps) etc. Using a few query

Comments

  • If a hierarchical relationship between two tables is represented by a foreign key in one table referencing the primary (or candiate) key in the other, adding additional hierarchical relationships will require schema updates and probably some database reorganization.

    A Junction Table (aka association table or association entity, etc.) would be preferred here because Junction Tables can /only/ be valued after both tables are populated. (Referential integrity constraints are in order.) Adding Junction Tables modifies the database schema, but requires no reorganization.

    But Junction Tables have their problems too. They don't really implement hierarchical relationships. They implement bipartite relationships. (See Wikipedia and/or Google for definitions.) A bipartite relationship can be interpreted as a hierarchical relationship, but there are limits.

    If you would care to look at a completely new way to do this, take a look at www.sfdbs.com, in particular, www.sfdbs.com/toplevel/fasttrack/fasttrack.shtml


    What you'll see is a third way to represent relationships I call aggregate-link. It will allow you to build arbitrary hierarchies over tables, and can be added arbitrarily (like Junction Tables) anytime after a database has been built. This would be appropriate in your case of 6 tables.

    Any foreign key, including a self-referencing foreign key can be implemented using this aggregate-link structure. This 2-level structure visualizes nicely.

    In the same way that two relationships between table pairs (A,B) and (B,C) can be joined , a pair of aggregate-link structures related by a common table (i.e., B) can be composed into a 3-level hierarchy.

    3-level hierarchies (and greater) visualizes very well indeed.

    Rob

    : I have a database that calculates costs of a company using a
    : managerial accountancy method (activity based costing).
    : The database uses 6 tables with information about the departments
    : (cost centers), activity of the departments (process steps) etc.
    : Using a few query
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion