4 Replies Latest reply on Dec 1, 2010 10:31 AM by InvectusIlumni

    Advice needed for simple church database relation diagram

    InvectusIlumni

      Title

      Advice needed for simple church database relation diagram

      Post

      Hi folks,

      I'm working a very simple church database solution which consists of 6 main tables:

      MEMBERS, CONTRIBUTIONS, EXPENSES, DEPARTMENTS, STAFF, ASSETS.

      This prototype church has many members and depends on their contributions and that of other good samaritans from the general public to survive. Like any other organization, this church has financial obligations (expenses), employs staff members, manages departments and owns assets.

      I need help figuring out the best way to create a relationship diagram.

      Here's what I already know:

      • A member can make many contributions, but a given contribution can only be made by one member
      • A staff member can process many expenses, but an expense can only be processed by one staff member
      • A staff member can belong to many departments and each department can have many staff members

      As you can see, I need some advice on how to best map this together so it makes sense. In other words how tables should be related to one another.

      Thanks a lot for any help.

        • 1. Re: Advice needed for simple church database relation diagram
          philmodjunk

          Members---<Contributions

          Departments---<Staff---<Expenses

          Seem the obvious relationships here, but others may me needed or useful to you.

          Assets----<Expenses could be used to link expense records to specific expenses so that you can track repair and maintenance costs for each asset.

          A staff person could be shared by two or more departments (One secretary might serve more than one department), in which case you'd need a join table for matching staff to departments.

          Department---<Dept_Staffing>---Staff

          Since staff and members data will largely consist of contact info and your staff may also be members, you might want to put all staff and member records in the same table, using an added field to identify who is on staff and another that identifies someone as a "member".

          Other detail tables may be useful. For example, these days, people have multiple email addresses, social networking links and phone numbers. You might manage all these with an added table linked to Members.

          • 2. Re: Advice needed for simple church database relation diagram
            InvectusIlumni

            Hey Phil,

            Thanks for the quick reply. I like you suggestion to merge STAFF and MEMBERS.

            Let`s call the new merged table CONTACTS

            What do you think of this new layout? Any suggestion?

            CONTACTS --< CONTRIBUTIONS

            CONTACTS --< EXPENSES

            ASSETS --< EXPENSES

            DEPARTMENTS -- <DEPARTMENT_STAFFING >-- CONTACTS

            • 3. Re: Advice needed for simple church database relation diagram
              philmodjunk

              It works, though some developers would merge contributions and expenses into a single table with contributions stored in one field and expenses in another. This allows you to set up a ledger type report where you can see contributions and expenses in different columns of the same layout with a running balance computed much like you'd see in a traditional check register where you might log deposits (contributions), withdrawels (Expenses) and then compute the current balance.

              • 4. Re: Advice needed for simple church database relation diagram
                InvectusIlumni

                Much thanks Phil,

                Your advice is exactly what I was looking for. I will indeed merge EXPENSES and CONTRIBUTIONS into FINANCE. This will simplify the diagram and give me more control over data. If you don't mind, I will consult you from time to time to get your thought on how somethings are best done.

                I haven't developed a solution in a while and I lost good habits in the process. I am rethinking my old ways and I wanna make sure what I do is in sync with today's accepted standards.

                Thanks again