Membership Table Question

[Linda]

First of all - I am not a new designer- just running into
a situation that is taxing my design brain cells, so I am
building a database for a religious education program
where the main tables include Family Info, Child Info,
Teacher Info. Because students sign up for either Spring
or Fall Programs (not both), and attend a variety of
schools in all grades Pre-K to 12, I have included Lookup
Tables for Program Year, Semester, Public School
Attended, Grade Level.
My question is: If a teacher is also in a registered
family (most are), but most families do not have a
teacher in the family, what is the best way to link the
family & teacher tables, or should I just leave them non
related. This seems to break the rule of non-duplication
of data within a database. Or, should I provide a field
on the family table that allows for teacher only
information. Can you see why I am confused? I need to be
able to have a table that links teacher, students, grade,
semester, & year for class lists. Anyone who can follow
this brain-dead mess and has an idea, please respond.
Thanks

 

[Allen Browne]

Human relations always defy the database relations.

If it is important to identify the persons in each family, you might
consider putting all the people into one table: tblPerson. If people have
special role in your organization (such as "Teacher" or "Admin"), it seems
that you would need a tblRole (lookup table of roles) and a tblPersonRole
(junction table with people's roles).

Once all the people - teachers, pupils and parents - are in the one table,
it makes it easy to group them into familes, classes, committees (which
might incorporate staff and student reps), etc.

 

[Linda]

Allen , I agree that that could work, but then I have to
deal with organizing family data together also, to keep
track of the financial side of things too.
I think I will have to have a yes/no link in the
tblFamily to tblTeacher . Otherwise I would never be able
to normalize the data. I think going the tblRole route
would be too complicated between mother, father, multiple
children in families, and mother or father could be
teacher/aide.
You are right about human relations defying a database.
When I volunteered to do this for my church I really
thought it would be easy to organize like my business
databases I have built. My high school class reunion
database with all the family info, and committee members
was easier to design than this. UGH!!!!!