Results 1 to 9 of 9

Thread: Design Question

  1. #1
    Join Date
    Jul 2007
    Posts
    6

    Design Question

    I am having trouble solving a db relational table design problem.

    The situation is this:

    The database is of an organisation, more specifically tracking the positions that members hold in that organisation.

    The organisation is split on 3 levels. Whole organisation, branches, and sub-branches.

    There can be many sub-branches for each branch, and many branches for the whole organisation. (ie in the whole organisation there are 20 branches, and each branch has around 4 or 5 sub branches, which are linked to those branches)

    Members of the organisation are members of individual sub-branches, however they can hold positions at a sub-branch, branch, or whole organisational level.

    Each member may hold more that one position, at more than one level.

    At the whole organisational level there are positions like (President, Secretary, Treasurer)
    At a branch level there are positions like (Branch President, Branch Secretary, Branch Treasurer)
    At a sub-branch level there are positions like Sub-Branch President, Sub-Branch Secretary, Sub-Branch Treasurer)

    Additionally, a very limited number of special subbranches, are linked to more than one branch.



    Can anyone help me think of a suitable relational db design for this?

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one table for organization components

    one table for the component hierarchy, with two relationships to components (parent-of, child-of)

    one table for positions, related to organizational component

    one table for members

    one table for member positions, related to both members and positions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Posts
    6
    thanks for that. thats really helping to get me started.

    Does this look right:
    {Hierachy - FKs: OrgsID (as parent), OrgsID (as child)} M-1 {Organisations - PK: OrgsID} M-M {Positions - PK: PnsID} 1-M {Mem/Pns - FKs: MemID, PnsID} M-1{Members - PK: MemID}

    Just a couple of questions:
    Does that still leave a many to many relationship between the organisational relation and the positions relation? One branch can have many positions (say branch pres and branch secretary), and one position (say branch president) can be held by many branches

    How do i get the db to take notice of the hierachy relation when running querys, for instance - selecting all sub-branch presidents in a (parent of) branch.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if there is something unique about the position "branch president" regardless of which branch the position belongs to, then yes, you'd need another table for that too

    how does the database "take notice" of the hierarchy? by the way you code your sql

    ... from org join hier on child join hier on parent join orgpos join pos

    sorry for using shorthand, but you started it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2007
    Posts
    6
    great, thanks for your help.

    will work on it tomorrow, im sure ill be back for more help.

    is that sql for heirachy able to be done on access?

  6. #6
    Join Date
    Jul 2007
    Posts
    6
    Can anyone help me create the Hierachy - Organisations join in MS Access

  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by thesimon
    great, thanks for your help.

    will work on it tomorrow, im sure ill be back for more help.

    is that sql for heirachy able to be done on access?
    Yes, you can do standard SQL manipulations, such as inner and outer joins, in Access.

    However, maintaining a hierarchy usually requires triggered procedures or some such technique to, for example, be able to find all descendants or all ancestors as in a typical tree. Unless you're using SQL Server behind Access, there's no way to do it with SQL. It's possible to do it in Visual Basic, but it's klugey.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi thesimon (and repsondents).

    Please keep all hierarchy implementation in Access details in this thread to avoid duplication:
    http://www.dbforums.com/showthread.php?t=1620031
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    New York Times - pootle wins gold in the lightweight division.

Posting Permissions

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