Design help for community group database

A

AnneS

My previous posts on this topic appear to have been deleted so I can't check
back on previous responses so I apologise for any repetition.

I am trying to create a database that allows me to track the training,
awards, roles, length of service etc of Girl Guide Leaders.

Requirements
1)Each leader requires a Guiding Partner (mentor) for both initial
qualification and further development. At each stage this may be a different
person.

2)Each leader may be a Guiding Partner, but is not necessarily so.

3)A leader may be a GP for more than one leader, and each leader may have
more than one GP.

4)Each leader is required to undertake qualification training and have that
“appraised†every 3 years by completing further training courses and
completing modules. Each module requires a GP

5)Long Service and Good Service Awards are presented and need to be tracked.

Following advice from Allen Browne (which I hope I have understood) I have
come up with the following tables, but I am still having trouble
understanding how to set the many-many relationships...do I have even have
the correct fields and correct primary keys I wonder? I understand setting
the relationships when there are only 2 or 3 tables, but with the 15 I have I
am still very confused. Perhaps I have misunderstood what to do with junction
tables.

tblLeaders
LeaderID (primary key)
Membership No
FirstName
LastName
District
Division
Region
HomePhone
WorkPhone
Mobile
Email
Address
City
State
Postcode
HusbandPartnerName

tblRoles
RoleID (primary key)
Role Name

tblRoleDetails (junction table)
RoleID
LeaderID

tblModules
ModuleID (primary key)
ModuleName
Freq
PeriodID

tblModulesCompleted
QualID (primary key)
LeaderID
ModuleID
StartDate
Endate


tblModuleDetails (junction table)
ModuleID
QualID

tblTrainingCourses
CourseID
CourseName
Freq
PeriodID

tblTrainingsAttended
TrainingID
LeaderID
CourseID

tblTrainingDetails (junction table)
CourseID
TrainingID

tblGuidingPartners
GPID
LeaderID

tblGP Allocations
GPallocateID
LeaderID
ModuleID
StartDate
EndDate

tblAwards
AwardID
AwardName

tblAwardsReceived
AwardsReceivedID
LeaderID
DateReceived

tblAward Details (junction table)
AwardID
AwardsReceivedID


*** Do I have too many junction tables? Is it possible to have one junction
table to relate all the tables and if so what would its fields need to be?

Thank for any help. I'm sorry to be so dense.
 
A

Allen Browne

Hi Anne

That's probably too big a question to answer in the groups here. We can't go
through tens of tables advising on the relationships.

I have mocked up a really basic example to illustrate just the tables for
the mentoring part:
http://allenbrowne.com/temp/GirlGuides.zip
It's a 46KB (zipped) MDB for Access 2000 or later.

In this example, tblClient holds everyone (guides, leaders, guiding
partners, etc.) tblRole lists the possible roles people may have.
tblClientRole lists the actual roles, so if client number 5 is a guiding
partner, they this table defines it. You can then use a query (like
qryGuide) to give you a listing of those who are guides, and a similar query
to give you those who are leaders, and so on.

tblMentor holds the information on who is mentoring whom. So, if client 1 (a
guide) is being mentored by client 3 (a leader), you have a record that
shows this relationship started on a particular date. Later, if client 1
will be mentored by client 2, you have another row that shows the date when
that started. You can then construct queries to show who is currently
mentoring whom.

I didn't create any forms, but hopefully it's enough to show how the tables
fit together. The Relationships widow gives the overview.
 
F

Fred

I think that you are better off on your current track, but FYI there is
software something like this called TroopMaster.
 
A

AnneS

Ah! Now I think I understand what you are on about. Being able to see your
working of my scenario in the relationships window, rather than just listing
here makes it a bit clearer.

Thanks so much for your time and patience with me.

I'll carry on and hopefully will end up with a successful database!

Anne
 
A

AnneS

Thanks Fred,
Yes I checked it out last time, but it really was too hard me to adapt.
Thanks for the suggestion though.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Design help for community group database 9
same field in more than one table 2
Many to Many relationship 1
junction tables and subforms - my brain is fried 1
Employee Training Help 1
Database Design Suggestions 3
Using 2 Autonumbers in 1 table 2
Junction table 1

Top