Table / organization question

[Rob S]

I work for a non-profit and am trying to combine two databases that we use
to track services we provide and also expand them to capture additional
information. We provide a number of different services ranging from
trainings, to brochures, phone support, and a lending library. Right now we
use a very archaic db to track "resource distribution" which includes
resources from our lending library as well as brochures and "gift items"
used at trainings (pens, notepads, bags, etc with our logo). We don't keep
track of our brochure inventory, though this is something that we would like
to track and get a better control of.

I guess my question is whether it makes sense to combine an inventory
tracking system that combines brochures, loans from the lending library and
gift items. The lending library part is the one that I'm having trouble
wrapping my mind around. I don't want to create a completely separate table
for it, but I'm not certain I'm creating a problem by combining it with
items that are true distributions.

Second question is also a structural question. I'm trying to keep the db
rules in mind with this db. We track who we provide services and resources
to for grant reporting purposes. In our current db we have the recipients
into two tables. One is for a listing of our members. The other is for
non-member organizations. If we receive a call from a member organization
we enter the date, staff member, organization name, topic, etc. If it is a
non-member calling we enter the date, staff member, organization type,
topic, etc. So we have two tables for phone calls, two tables for mailings,
two tables for resource distribution. Seems very redundant. I think I can
create one table that ultimately tracks all phone calls, one that tracks all
mailings, etc.

What I want to do is select whether member or non-member. When you select
member it would then provide a drop list of our member organizations. Some
member organizations have offices in one city, others have them in three or
four cities and I want to track where the caller is from. If non-member is
selected then it provides a list of caller types (high school, university,
medical, etc). The problem is that once you select a caller type I'd like
to then be able to type in who the caller is by name of organization. In
addition to this info, then there is more detailed questions about the call,
such as topic and notes, staff member and date.

I've learned how to change drop-down selections based on the selection of
another drop-down box. So it seems like I should be able to select Member
and and then Member Name and then City or Non-Member and Member Type. But
should this be stored in one table or two?

Thanks
Robert

 

[njem]

Not sure these solutions are right but here's my take, going for the
keep-it-simple angle.

For the distributed items/lending library db you could just make a
field that indicates whether this item has been given as a gift or is
expected back, and if it's to come back then you could add info about
when, etc.

For where people are calling from, your drop down could simply have
one list that includes Partner Org A, Partner Org B, and generic
entries for high school, university, etc that are not partners. Then
some additional info field can be filled in with a name if it's not a
partner.

 

[Bob Quintal]

I work for a non-profit and am trying to combine two databases
that we use to track services we provide and also expand them to
capture additional information. We provide a number of different
services ranging from trainings, to brochures, phone support, and
a lending library. Right now we use a very archaic db to track
"resource distribution" which includes resources from our lending
library as well as brochures and "gift items" used at trainings
(pens, notepads, bags, etc with our logo). We don't keep track of
our brochure inventory, though this is something that we would
like to track and get a better control of.

I guess my question is whether it makes sense to combine an
inventory tracking system that combines brochures, loans from the
lending library and gift items. The lending library part is the
one that I'm having trouble wrapping my mind around. I don't want
to create a completely separate table for it, but I'm not certain
I'm creating a problem by combining it with items that are true
distributions.

Second question is also a structural question. I'm trying to keep
the db rules in mind with this db. We track who we provide
services and resources to for grant reporting purposes. In our
current db we have the recipients into two tables. One is for a
listing of our members. The other is for non-member
organizations. If we receive a call from a member organization
we enter the date, staff member, organization name, topic, etc.
If it is a non-member calling we enter the date, staff member,
organization type, topic, etc. So we have two tables for phone
calls, two tables for mailings, two tables for resource
distribution. Seems very redundant. I think I can create one
table that ultimately tracks all phone calls, one that tracks all
mailings, etc.

What I want to do is select whether member or non-member. When
you select member it would then provide a drop list of our member
organizations. Some member organizations have offices in one
city, others have them in three or four cities and I want to track
where the caller is from. If non-member is selected then it
provides a list of caller types (high school, university, medical,
etc). The problem is that once you select a caller type I'd like
to then be able to type in who the caller is by name of
organization. In addition to this info, then there is more
detailed questions about the call, such as topic and notes, staff
member and date.

I've learned how to change drop-down selections based on the
selection of another drop-down box. So it seems like I should be
able to select Member and and then Member Name and then City or
Non-Member and Member Type. But should this be stored in one
table or two?

Thanks
Robert

My opinion is that you need a handouts table, a books table, a
Library loans table, a people (members and non-members) table, an
organizations table, a calls table, a mailing table and a people-
organizations table.

Brochures and pens are all handouts.

Library books may be loaned out to several people over time, so you
have a books table joined one to many with the loans table to log who
loaned a book, when and when they returned it the people table is
also joined one to many to the loans table. This makes the
relationship of books to people many to many through the loans table.

Apply the same principles to mailings, as one person may get several
mailings and mailings go to many people. Apply the principles again
to handouts,

As to the reason for having a separate people and organizations
table, people may belong to multiple organizations and organizations
are by definition, several people.