Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    1

    Setting parameters at Hierarchy within a hierarchy

    I have a situation where I have two hierarchies : the Oragnization
    hierarchy which has 6 levels, for example
    Firm
    Entity
    Business_Unit
    Division
    Region
    Office

    and the second hierarchy is
    Business_Group
    Product_Group
    Product
    Sec_Type
    Sec_Sub_type

    I have resolved the above two hierarchies by creating 1 table at each
    level and setting up a 1:M relationship.

    From a processing perspective, the business rules requirements are to
    be able to set parameters at
    Firm
    Business_Group
    Product_Group
    Product
    Sec_Type
    Sec_Sub_type

    Entity
    Business_Group
    Product_Group
    Product
    Sec_Type
    Sec_Sub_type

    and so on ..... all the way down to the account level. Parameters set
    the lowest level will override the values at a higher level. No matter
    where the parameters are set, the requirements is to be able to see
    the values at the ACCOUNT - SEC_SUB_TYPE level.

    My first thought was to create one table for every level meaning Firm
    - Business_Group, Firm-Product_Group etc. etc. ..... Then I have
    thought of completely de-normalizing the hierarchy and carry all
    combinations in 1 big table. Both seem a little extreme, although I
    must confess I am leaning towards the latter. I realize that the
    maintenance would be higher because of repeating elements but the
    parameters will be changes no more than once every couple of months.
    Also the number of records on both sides are fairly small.

    This matrix that I am thinking about will be at most a cartesian
    product between office and Sec_Sub_type. There are about 30 parameters
    that need to be set in this manner.

    Any thoughts on this ? One one side I want to normalize this, because
    that what makes sense to me, but then on the other side, I am thinking
    why do it and create 30 some tables in the process ? Since everything
    has to be resolved down to the lowest level, why not just create a
    matrix.

    This is an important design consideration for me since there will be
    other parameters that may follow a similar pattern in the future and
    performance is very important.

    What do you all think ?

    Regards
    Harry

  2. #2
    Join Date
    Mar 2003
    Posts
    6
    You should probably think about the way the access of the datasets is likely to be. Try to optimize for the actions, which are performed the most. Will the datasets be accessed as a whole (meaning, do you need all information for each dataset) or do people more likely only access the lowest level of information (meaning the lowest parts of the hierarchies).
    If you need to display all or mostly all information at a high access percentage, then denormalisation will probably be the solution, rather than normalisation. And the other way round in the contrary case.

    Regards Ursula

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •