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

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

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.

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

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?

Can anyone help me create the Hierachy - Organisations join in MS 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.

Hi thesimon (and repsondents).

Please keep all hierarchy implementation in Access details in this thread to avoid duplication:
Displaying hierachical data in access

pootle flump ur codings are working excelent.

New York Times - pootle wins gold in the lightweight division.