Table Relationships

[rdholdford]

I am developing or rather amending an existing database for a non-profit and I am having difficulty visualizing table relationships

The agency wants one master database. It has 12 programs (may add more) that serve families to prevent child abuse/neglect. Families can have members that are clients or non clients. Client can be pending, active or closed in multiple programs with varying open and closed dates

The current version has two main tables, FAMILY – which includes a Family ID field case name and demographic and contact data and CHILDREN – which includes some demographic data on each child. These tables are related, one to many, with FAMILY being the “parent”. Each of these tables also has a “child’ table, FAMILY SESSIONS and CHILD SESSIONS.

The problem is the agency has 12 programs. Some focus on adults, some focus on kids and some focus on families. I must keep track of adults and children and various demographics with participation in sometimes multiple programs with various participation dates and case status information (pending, open, closed). I must also track for each program and for the agency as a whole

Families can have many members. Members can be in many programs. Programs can have many members. It seems I need a PROGRAM table but I’m not clear on how the tables should be related. Thanks

 

[willir]

Hi rdholdford

Have we discussed this database in the past??

Anyway, you will need a table for your programs...

tblPrograms
ProgramCode - short text code, primary key
ProgramName - text
ProgrameAudience - short text (adult, youth, postnatal, etc)
Comments - memo
...plus other stuff relavent to the program

You may also wish to capture program dates for scheduling. Best to use another table for this so you can capture history, and determine which programs are currently full, next available program.

tblProgSchedule
ScheduleID - primary key
ProgramCode - foreign key to tblProgram
StartDate - date field
StartTime - date field (formatted as time)
EndDate - date field
EndTime - date field (formatted as time)
FacilatatorID - foreign key to tblFacilatator
Capacity - numeric, interger
Fee - numeric, currency
Comments - Memo

Note the requriement for an instructor or perhaps more aptly, a facilatator.

tblFacilatator
FacilatatorID - primary key
FacilatatorLN - last name
FacilatatorFN - first name
...plus other contact info

Then you need to "book" clients to a program. I will use the name tblRoaster

Before doing this, please realize that although you have "family" and "children", you should perhaps consider the table for "client" which would consist of the "children" and the guardian. Why? Well this info may be different from the "family", and there may be more than one guardian (mother / father or previous and current foster mother and father. From the "client" table you can then specify the type of "client" such as child or guardian. The purpose of the "family" table is really only to all the siblings, and/or the default / primary guardian.

For the sake of this disucssion, I am going to assume...

tblClient
ClientID - primary key
FamilyID - foreign key to tblFamily
ClientType - short text (child, guardian, custodian, etc)
ClientLN - last name
ClientFN - first name
...plus other info for the client

tblRoaster
ClientID - foreign key to tblClient
ScheduleID - foreign key to tblProgSchedule
RegistrationDate - date field when registered
PaidFee - boolean (yes / no)

With this design you can then register specific members of a family to a program, or all members. Note that this uses a relationship called "many-to-many". One client can "attend" (or belong) to more than one program. One program, more specifically a ProgSchedule, can have many clients.

...Moving on
One thing not discussed is how to assign guardians. I am sure your existing procedure works for you. (Unfortunately I can not see how since no info has been provided) But one apporach, which is very similar to the Roaster table, you can use a many-to-many relationship which provides more flexibility, or a one-to-many relationship which is less complex.

For Many-to-many:
A child can have more than one guardian
A guardian can have more than one child
tblGuardianChild
GuardianID - foreign key to ClientID on tblClient for a Guardian
ChildID - foreign key to ClientID on tblClient for a child

For One-to-many, modify tblClient, add...
GuardianID - foreign key to ClientID on tblClient


Okay, why use 4 tables to track your info?
- You need the owner or fascilatator or person responsible
- You need the program name and details
- You need a program schedule table to show dates, etc. Otherwise, you will have to keep changing the program table
- You need a roaster table to capture who is "in" or attending the specific pgraograms. Note that the roaster table links to the program schedule table which in turn links to the program table. This is why the program code short code is nice to use. You can identify the program without having to directly link to the program table.

Richard

 

[PHV]

Richard,
why 2 date fields for a DateTime one ?

 

[willir]

Yea - good point. From my perspective, it is easier for date and time to be handled separately, epsecially for a less experienced user. But you are quite correct -- they are the same -- just need to separate the interger part for the date, and decimal part for the time.

Comments??

 

[rdholdford]

Thank you very much. This is just what I needed. I really appreciate the support