Tables and Relationships and relating them to reality

[Guest]

I’m trying to help a non-profit organization in my spare time (of which I
have none). I volunteered to set up a database having no idea it would be
this never-ending maze and that learning ACCESS is nothing like the other MS
Office programs. After numerous ACCESS tutorials, I have come to the
conclusion that although forms and queries all seem relatively understandable
once your tables are set up, I am not really sure how to set up my tables and
their relationships! The real problem is that you have to follow the logic
of why you’re doing it in the first place and I haven’t quite got that yet.

Basically the information I’m working with can be characterized as a contact
list. There are new queries that come in and older contacts. Some of these
contacts also turn out to be donors, although at this point I’m more
interested in sorting out who is interested in which type of program. Some
of the programs are educational and have prerequisites. Some are purely
social. We would also invite people to different types of programs based on
their expressed interests, location, gender, age, and so forth. We would
also track people so that we could know when they were last seen at a program
or what kinds of programs they have come to in the past. We would also keep
a list of their interests in case we come up with new programs. We would
also keep a list of people’s skills that we might access.

I came up with one really big table with about a kazillion fields. But for
the new people I felt I should have a second table although some of the
information is the same and later they might end up on the not so new
people’s table. I am in doubt about this. For example, with new people we
want to know where they heard about it – was it the website, a flyer, word of
mouth, etc. We also want to track our followup carefully if they express
interest in certain ongoing programs. If they express interest in YTT, we
want to know when they contacted us, how they contacted us (phone, email,
etc.), when we called them back, if they are signed up for the next program
and if not why. Also I was wondering if I shouldn’t split the big table
because you can’t follow the data when you get to the end of it. Or I guess
that wouldn’t matter if you’re actually inputting in forms and taking data
out with queries. I’m still thinking Excel I guess.

Any suggestions to get me going?

 

[Ed Warren]

Try taking a look at UML modeling. It helps identify 'Classes' (approx
equal to the tables you need)

Example:
In your discussion below you identify:

People
donors
donations (implied)
programs
programType (social, Educational)
programPrequisites
programOfferings (date,place, program,group, etc)
programInvitations (invitation to join a program)
Interests --List
IndividualInterests
Locations
IndividualLocation
Gender (male, female, not reported ... (note in some medical records there
are 7 genders)
ageGroups (may not actually be a table but implemented as business logic
e.g. 12-17, 55+)

ProgramParticipation
Skills

-------

hidden may be Companies (donors that are not people)
hidden are ProgramOfferings (a program offered at a place on a specific
date -----).


As a first cut go through your write up below and circle all the nouns (you
have a start on the classes), note some that you will need are 'hidden'
and/or implied e.g. donations.


You will need exactly the opposite of what you have developed, you will need
a "kazillion" tables with a few fields each.

Then you are ready to start work on queries, forms, and reports.

Lots of Luck (Note: my experience in the same type of volunteer project took
18 months and about 100 emails to complete so hang in there

Ed Warren.

 

[Steve Schapel]

Carolyn,

Thank you for the very clear explanation of your project. Some of your
self-diagnisis (e.g. "I’m still thinking Excel") is absolutely correct.

Most people find the learning curve to use Access correctly to be fairly
steep. And you are "cutting your teeth" on a pretty complex system.
Haven't you got a nice little stamp collection or something that you
could practise on? LOL,

Anyway, you are quite right in thinking that it is important to get your
table structure sorted. This is your #1 job. You should try to allow
the actual structure of the actual data to determine the design of your
tables. In particular, where there is a one-to-many relationship
between different elements of the real-world information, this means two
related tables. For example, the programs people are interested in has
to be in a separate table from the core contact information about the
people. But this is not because a big flat wide spreadsheet-like table
is unwieldy. It is because you are using a relational database, and
since any given person can be interested in more than one program,
database design principles dictate that this information gots in a
separate table. I have not analysed what you have told us about your
project, so this is completely "off the top of the head", but I would
expect you will need in the order or 20 tables.

You might find this information helpful...
http://support.microsoft.com/?id=283878

Also, I have been reading the new book "Building Microsoft Access
Applications" by John Viescas, and it has an excellent section on table
design.

 

[Ed Warren]

Also you will want to store 'date of birth' , then calculate 'age' since
'age' changes over time and date of birth is a constant (at least for some
of us)

Ed Warren

 

[Steve Schapel]

Carolyn,

A further comment from me as well... It would be a mistake to put old
people in one table and new people is another table. If you need to
designate old from new, this should be done via a new field added to the
table where you enter old or new.

 

[Guest]

Thanks so much for this good start. I begin to see up which wrong trees I am
barking??? I will go and digest this and see how far I get. Thanks again.

 

[CS]

I am a similar "cutting my teeth" phase, performing work on a volunteer
database job for a non-profit, and have walked the same road (many
tutorials, and much googling, etc.).

Finally, after attempting to design on the fly, I resorted to printing out a
flow-chart that I cooked up in Excel -- (I first listed every piece of
information I thought I would need, broke that information into separate
fields where needed - ie. address street, city, state, zip rather than just
address -- then grouped the fields in tables that I thought were
appropriate, and saw which tables would require relationships to one
another). It was enormously helpful for me to lay the db out on paper and
get a true "overview" of how the parts needed to interact. As I said,
perhaps old-fashioned, and it did take some time, but it "put my head right"
with the Access concepts and possibilities. Now, as new information is
needed, I can pop that into my visual model before changing the DB design.

It was also very comforting to me when an Access-savvy friend assured me
that I was not, in fact, stupid, and that what I was attempting to do with
Access was
fairly advanced (Tracking large numbers and types of events, donors,
members, non-members, etc.), and
went beyond the "little stamp collection" that Ed Warren
mentioned.

I'd be happy to be in direct contact with you by email and offer you any
insights/information on how I'm going about this. It sounds as if our
projects are
similar. If that's of interest to you, you can email me directly by
removing "NOTTHIS" from my email below.
Carol
(e-mail address removed)

 

[Ed Warren]

Sorry, but I have to give the stamp collection idea to Steve Schapel.

Actually, I have found there are few (if any) 'simple' database projects.

Bet you that if one really got into it even a stamp collection can become
complex. I know my 'simple' little database to track video tapes has grown
in complexity over a short period of time.

Ed Warren.

 

[CS]

LOL! Yes, isn't it a crack-up how much information we can become convinced
that we need (and yet, there is always that field that I didn't think to
include . . . )
In considering it, I could imagine that a stamp collection could be
incredibly complex, especially if one was banking on it as a future value.
(ugh!)

Take comfort Ed, my video tapes are completely un-Accessed, and as a result,
they wander off regularly with friends and family. (Hmmmm -- my next Access
experiment?)

Anyway, I appreciate your humor and participation in this forum.
Carol