Table desing re-do

[Stephm]

Hi. I am sad because I think I need a re-do. There are
two inter-related criteria that need to be met. The
current table structure takes care of one of the issues
but fails miserably at the other -Logic constraint. You
might disagree, but composite primary keys are EVIL.
Please let me explain the situation and see what you
suggest, if you have time- I apologize that this post is
so long. We have 2 types of volunteering going on: On-
going program opportunities and one-off volunteer
opportunities. Contacts "certify" with their Animals
(through National Registration- this works!) and
volunteer together (or Contacts alone).

On-going Program opportunities: We need an Organization
(Shriner's) where we volunteer. We need a Program name
(Shriner's Facility) and we need a Start and End Date (in
case we ever terminate a Program). I'd like an On-Going
Program flag. We need to know the Days (multiple) we
visit, the Frequency (1x week, 2x a month), and the
Timing (every week, alternate weeks, week-one of the
month), the Start and End Time. Then, I want to assign
Contacts/Animals the Program on the Contacts form and be
able to identify which Contact is the Program
Coordinator. In addition, I need to keep track of the
Start and End dates that the Contact is associated with
the Program. I would have a form to set up the Program
Opportunities. Then to assign them, I envision a subform
on Contacts with combo box to choose the Program name
(which defaults the Organization), a flag to check for
Program Coordinator, a combo box to select the Animal,
and a Start and End date for the Contact association with
the Program (not the Start and End date of the Program
itself). This is the issue that doesn't work. I have no
way to make these on-going program opportunities semi-
specific to the Contact (e.g. Susie volunteered from
01/01/03 to 01/05/04 with "Waldo" and from 01/06/04 to
01/07/05 with "Lucky" and then Susie stopped visiting
this program). Originally, I assigned Contacts on the
Program form. Totally backwards it seems.

one-off) Volunteer opportunities: MADD (Organization)
invites us to bring Therapy Animals to help grieving DUI
victim families at "Don't Drink Day" Volunteer
Opportunity. It's a one-time Volunteer Opportunity.
Tents, performers, petting zoo. I volunteer with one of
my certified Animals, here I choose Gertrude. We need
the Volunteer Opportunity Date, Volunteer Opportunity
Name (similar to Program Name- could use same field),
Organization, Start and End Times, number of
Contact/Animal teams invited. Everyone who volunteers at
this Volunteer Opportunity earns the same number
of "Bucks". I want to assign Volunteer Opportunity to
Contacts on the Contacts form. I would have a form to
set up the Volunteer Opportunities (similar to Program
Opportunities but segregated by the On-Going Program
flag). Then to assign these Volunteer Opportunities, I
envision a subform on Contacts with a combo box to select
the Date which would have the Organization, Volunteer
Opportunity Name, and "Bucks" default. And then a combo
box to choose the Animal. This actually works, since
there is nothing specific about the Contact/Animal
assignment (meaning no individual info about the Contact
needed to be written).

I want an On-Going flag as an Organization may have
multiple associations with us (On-going program at
Shriners, then a one-time Shriner's Child Safety Fair
Day).

I'm not sure I can be salvaged. Maybe lobotomized?

Thanks- sorry this post is so long and complicated. I
appreciate any design help you can give- details
appreciated as I sadly sometimes get the joins backwards.

 

[Tim Ferguson]

We have 2 types of volunteering going on: On-
going program opportunities and one-off volunteer
opportunities. Contacts "certify" with their Animals
(through National Registration- this works!) and
volunteer together (or Contacts alone).

[long snip...]

I have to confess that I did not understand much of what you posted. I
did manage to glean the following entities, although I don't really know
how they interact: (*=primary key, +=foreign key)

Programmes(*ProgrammeNumber, Title, Description, Organization+, etc)

OneOffVolunteerOpps(*ProgrammeNumber+, StartDate, NumOfPlaces, etc)

OngoingProgrammes(*ProgrammeNumber+, DateStart, DateEnd, etc)

Animals(*RecordID, TypeOf, Owner+, DateOfBirth, etc)

Certifications(*AnimalRecordID, *ProgrammeID, DateOfVisit, etc)

Organizations(*OrgCode, ContactName, ContactAddress, etc)

Volunteers(*VolNumber, FullName, HomeAddress, etc)

Applications(*VolNumber, *ProgrammeNumber, BucksEarned, etc)



etc, etc. I don't know how much that will help, but you do need to forget
all the stuff about flags and combo boxes until you have your table
structures really nailed down and tested. Tables and relationships first;
keys and attributes next; user interface last.

Hope that helps


Tim F

 

[Stephm]

Thanks for the reply. While I do see the beauty in Tables
and relationships first; keys and attributes next; user
interface last- I've gotten burned by not having a vision
of what the end usage is going to be. Cheers.

-----Original Message-----

[email protected]:

We have 2 types of volunteering going on: On-
going program opportunities and one-off volunteer
opportunities. Contacts "certify" with their Animals
(through National Registration- this works!) and
volunteer together (or Contacts alone).

[long snip...]

I have to confess that I did not understand much of what you posted. I
did manage to glean the following entities, although I don't really know
how they interact: (*=primary key, +=foreign key)

Programmes(*ProgrammeNumber, Title, Description, Organization+, etc)

OneOffVolunteerOpps(*ProgrammeNumber+, StartDate, NumOfPlaces, etc)

OngoingProgrammes(*ProgrammeNumber+, DateStart, DateEnd, etc)

Animals(*RecordID, TypeOf, Owner+, DateOfBirth, etc)

Certifications(*AnimalRecordID, *ProgrammeID, DateOfVisit, etc)

Organizations(*OrgCode, ContactName, ContactAddress, etc)

Volunteers(*VolNumber, FullName, HomeAddress, etc)

Applications(*VolNumber, *ProgrammeNumber, BucksEarned, etc)



etc, etc. I don't know how much that will help, but you do need to forget
all the stuff about flags and combo boxes until you have your table
structures really nailed down and tested. Tables and relationships first;
keys and attributes next; user interface last.

Hope that helps


Tim F



.

 

[Tim Ferguson]

While I do see the beauty in Tables
and relationships first; keys and attributes next; user
interface last- I've gotten burned by not having a vision
of what the end usage is going to be.

And this is not being burned..?

"I'm not sure I can be salvaged. Maybe lobotomized?"


Tim F