Table Design 2NF

[Guest]

Hi I am looking for assistance with design of table.
I need to track Event and Participants. I understand that I will need an
intersection table to Join Event and Participants since one person can
participate in many events. Where I am getting stuck is that I also need to
track the roles each participant plays in each event. I participant can
play simetanous roles within one event. For example the participant can be a
paying attendee, as well as a volunteer, or an artist. I have set up tables
as follows:

Contact:
Contact ID
Name

ContactAffiliation: ( Affiliation is the identification of the individual
as an "artist", "volunteer", or "event attendee" of events)
ContactID
AffiliationID
Each contact in the organization is flagged with these catagories when they
are either entered in the db or when the sign up for a specific event)

Events:
EventID
EventName
EventDate

EventParticipants*:
EventId (FK) to Events
ContactID (FK) to ContactAffiliation
AffiliationID(FK) to ContactAffiliation

Sample Data:

EventID ContactID Affiliation
1 1 1 (artist)
1 1 2 (volunteer)
1 1 3 (event attendee)

Is this design correct?

Thank you inadvance for your assistance.

I am new to posting in forums, so please let me know if you need additional
information.

 

[Guest]

I think understand the difficulty you have is that the people, events and
roles creates a circular relationship ie each pairings are many-to-many.

I have a solution here (but could be improved upon possibly)

tble_event
EventID (PK)
EventName
EventDate

tble_person
PersonID (PK)
PersonName

tble_role
roleID (PK)
roledesc

tble_ER
PersonID (FK)
EventID (FK)
RoleID (FK)

Put the "tble_event" fields in the main form

Put the "tble_ER" fields in the subform (personID and RoleID)

PersonID is a combo box

Use "tble_person" as the source for the list.

bound column: 1
column count: 2
column width: 0cm;8cm

Repeat this for RoleID

You will see the name and role but it will store the ID numbers.

The problem you have is that you will have to enter the names multiple times
for each role.

Hope this helps.

 

[Guest]

SQL for the query will give you the data you need:

SELECT tble_event.EventID, tble_person.PersonID, tble_role.roleID,
tble_role.RoleDesc
FROM tble_role INNER JOIN (tble_person INNER JOIN (tble_event INNER JOIN
tble_ER ON tble_event.EventID = tble_ER.EventID) ON tble_person.PersonID =
tble_ER.PersonID) ON tble_role.roleID = tble_ER.RoleID;

 

[Guest]

It occurred to me that you do have what you need with my design:

Each event can have many people
Each person can attend many events

Each event can have many roles
Each role can be at many events

Each role can have many people
Each person can have many roles

 

[Guest]

Thank you very much for the quick response. I see where you are going with
this and in sql provided for the query.

One issue that I thought of after my posting is with the People records.
The People record may have two names - husband and spouse. Normally the
couple is tracked in a single People record with a membership name. To this
point this method has not presented issues, since Volunteer details are only
just now starting to be tracked. The issue now is that if one of the two
individuals (husband or spouse) has offered to volunteer, the name of the
volunteer is needed and needs to be associated with specific events that the
volunteer has participated.

The specific volunteer name and details regarding services offered is
tracked through an additional table "Volunteer Profile" where the ID from
the People Record is recorded and the name of the volunteer is indicated and
a volunteer id is assigned. I realize that this may not be optimal
solution. The best would be to create hierarchy with i.e. household group
and associate individuals to the group, but this suggestion has been vetoed
by client.

So if the role of the event participant is volunteer the VolunteerID will
need to be recorded as well as there ContactID. Where should the
VolunteerId be stored? In the Events table as:
EventId
ContactID
VolunteerId
EventDate

or in the table EventER
ContactID
VolunteerID (FK to VolunteerProfile table)
EventId
RoleID

I hope my explanation is clear.

Thank you again!

 

[Guest]

Actually I got this figured out. Thanks again for the response.
Your explanation of what is going on with the relationship was very helpful.