Approach for multi level organizations

[Frank Situmorang]

Hello,

I have created for our local churches a membership database. My question is
what is our approach to make a membership database for the following level of
our organization (bottom up):

1. Local churches
2. Mission/Conference ( consists of local churches)
3. Union ( consists of Missions)
4. Divison ( Consists of Unions)
5. General Conference (World level) consists of divisions

How can I make a database in the Mission level to be able to import data
from local churches ( note that church database consists of many tables)

The same routine import job is true for the other higher organization,
Union, Division and General Conference

What I have in mind, correct me if I am wrong, in the member table, there
should be a foreign key for Mission table...

Thanks for any idea provided.

 

[Allen Browne]

One approach is to use the tables as you have them, so each level is a
member of the next one up, and you can trace them.

An alternative approach is to put all the persons and corporate entities in
the one table of "clients." You can then define the connections between the
various clients, e.g. many people belong to a local church, and so on. This
approach has some advantages and some disadvantages.

A major advantage is that you have only one table to search to find
anything. Similarly, if you have to track things (like who paid for what),
you can just use a foreign key to the client table. This makes it really
flexible, and so I constantly lean towards this design.

The disadvantage is that it potentially makes things too flexible, and it is
possible to enter data that doesn't make sense. For example, you could
finish up with a division that is a member of a local church (which makes no
sense), so you have to take action to prevent this.

For a downloadable example of this kind of structure, see:
People in households and companies - Modeling human relationships
at:
http://allenbrowne.com/AppHuman.html

 

[Frank Situmorang]

Allen,

Thanks for your input, but mine is maybe not too complex. A member can not
belong to the next 2 or 3 upper level.

A member can belong to a local church, then a church can belong to a mission
then a mission belongs to a Union and a Union can belong to a Division and a
Devision belongs to a General Conference ( World Head Office).

I think what I want to try to make is upto Union. What I plan to make is

1. local church table
2. Mission table
3. Union table.

The table structure is
1. Primary key of local church - Foreign key in members table
2. Primary key of a mission table - foreign key in local church table
3. Primary key in a Union table - foreign key in mission table

my question is, in the mission table office, how can we import the data and
make a consolidation from all local churces

The same is true for office of Union table.


Thanks very much for any help

 

[Allen Browne]

How can you import the data from the various organisations? That's a big
question.

The anwswer will depend on how many factors, such as:
- How the levels of your organisation provide their data to you
(Excel? Comma-Separated-Values? Other?)

- How valid that data is when it arrives (relationally correct, required
fields all present, data types correct, etc.)

- Your mechanism for identifying the foreign key for the parent of the data
being organised.

Typically, it will involve importing into a temporary table, running all the
validation tests, getting the user to sort out any critical problems with
the import data and giving warnings on unlikely data, getting input from the
user for other info you need, inserting new values into lookup tables,
appending new records to existing tables, and deciding how/when to update
existing records or to just ignore any differences between existing records
and existing data in the import file.

 

[Frank Situmorang]

Allen,

How can we import data from the various organization is one of my question
to you, but what I have in mind is local churches will send their softcopy
of their Access data to the regional mission, and the mission office will
then import it into it's database. Then mission officess will send their
databases to Union office then in the Union office lelvel will consolidate it
in order to know how many members all together in the Union Area. Like in
Indonesia, we have Wes Indonesia Union and East Indoensia Union.

I know this is not an easy job, I think but who know I can design it because
for Him is nothing imposible.

So what is is your suggestion. Make a data export procedures in local
churches, then they send it to higher level of organization?. I propese the
data format is access data, not an excel

Fior your info. my local church membership database will be used in all
over the Mission area....,

Thanks for your idea

 

[Allen Browne]

You could write code to CreateDatabase, and export the Access tables, but
I'm not sure that's necessary. It might be easier to TransferSpreadsheet so
it exports to Excel, and then use TransferSpreadsheet again to import it
into the database.

 

[Frank Situmorang]

Thanks Allen, I will try your seggestion