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?
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.
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.