rather complex db structure!?

[Jerome]

Hi,

I'm trying to create a DB to planify events during a festival!

So far I've got the table containing the events: no problem.
I've created an online form so people where they can register and pick
dates and times that suit them: no problem.
I've created functions to import those data into the Access DB: no problem.

The festival goes on for 10 days and there a 4 possible time slots per
day. So how do I now make a relation between each event and the days &
time slots!?

I'd need to first define what event occurs on which days and at what
times. Something like this:

Event-1
Day1 Day2 etc
08.30 yes no
10.15 yes yes
14.00 no no
15.45 no yes

Event-2
Day1 Day2 etc
08.30 no yes
10.15 no yes
14.00 no yes
15.45 no yes

etc (there'd be some 50 events)

Then I'd need a form to bind the registrations to time slots that are
adequate and still free. Something like this:

Registration1

Event-X on e.g. 10/10/05
08.30 still free
10.15 taken
14.00 taken
15.45 still free

And I'd choose 08.30 as time slot for example.

I'm really not a beginner in Access, but I'm a bit at loss here as how
to construct the tables and how everything is interconnected ...

Any help or link is greatly appreciated!

Jerome

 

[Jack MacDonald]

One approach is to use four tables:

tblEvents
- EventID PK autonumber
- EventName text
- other fields about the event


tblTimeSlot (contains 4 records)
- TimeSlotID PK autonumber
- StartingTime DateTime values 8:30, 10:15, 14:00, 15:45


tblDateTimeSlot
- DateTimeSlotID PK autonumber
- EventDay DateTime -- stores the actual date of the event
- TimeSlotIDfk number
(Unique index on EventDay & TimeSlotIDfk to prevent duplication)


tblEventDateTimeSlot
- EventDateTimeSlotID PK autonumber (optional, but recommended)
- EventIDfk number
- DateTimeSlotIDfk number
(Unique index on EventIDfk & DateTimeSlotIDfk to prevent duplicate
records being created)

tblEventDateTimeSlot contains one record for each event scheduled into
a particular timeslot. Once you have built the tables, you build
forms to populate them with records. Use combo boxes to display lists
of valid values for the user to choose from.

Hi,

I'm trying to create a DB to planify events during a festival!

So far I've got the table containing the events: no problem.
I've created an online form so people where they can register and pick
dates and times that suit them: no problem.
I've created functions to import those data into the Access DB: no problem.

The festival goes on for 10 days and there a 4 possible time slots per
day. So how do I now make a relation between each event and the days &
time slots!?

I'd need to first define what event occurs on which days and at what
times. Something like this:

Event-1
Day1 Day2 etc
08.30 yes no
10.15 yes yes
14.00 no no
15.45 no yes

Event-2
Day1 Day2 etc
08.30 no yes
10.15 no yes
14.00 no yes
15.45 no yes

etc (there'd be some 50 events)

Then I'd need a form to bind the registrations to time slots that are
adequate and still free. Something like this:

Registration1

Event-X on e.g. 10/10/05
08.30 still free
10.15 taken
14.00 taken
15.45 still free

And I'd choose 08.30 as time slot for example.

I'm really not a beginner in Access, but I'm a bit at loss here as how
to construct the tables and how everything is interconnected ...

Any help or link is greatly appreciated!

Jerome

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Dave S]

The relationship between events and time slots is one-to-many so create a
table with the 10 time slots in it. Now each event record can have a variable
number of time slots assigned to it be using the prmary index of the event
table into the foreign key field of the time slot table.

You've got a bunch of participants who have signed up in another table(?)
mapped to a variable number of events and/or time slots (you weren't too
clear on that)? That would seem to be another one-to-many relationship
between the participants and their events/(or time slots?).

Now, you want to tally # of participants to each event (one-to-many as
seen from the event side), plus you need to tally multiple events to each
participant (one-to-many as seen from the participants side so their printout
can inform them of what events/time slots they've signed up for). This is a
many-to-many relationship between events and participants.

For a many to many relationship, create another table with 2
fields,representing foreign keys for primary index of event table and primary
key of participant table. The primary index of this table will be a composite
primary key defined from both fields. Now each user that signs up for an
event gets his primary key inserted into that table. Use the primary key of
the event table to fill the other column.

This additional table represents # of events for each user AND # of users
for each event. just count up the # records containing whatever event ID
you're interested in or count up # of records with the userID (and resolve
the eventID) to inform the user of what they're signed up for. Use lookup
fields with this table to make things more visually appealing.

Hope that was clear.

Dave

 

[Jerome]

Thanks, I'll try that!

One approach is to use four tables:

tblEvents
- EventID PK autonumber
- EventName text
- other fields about the event


tblTimeSlot (contains 4 records)
- TimeSlotID PK autonumber
- StartingTime DateTime values 8:30, 10:15, 14:00, 15:45


tblDateTimeSlot
- DateTimeSlotID PK autonumber
- EventDay DateTime -- stores the actual date of the event
- TimeSlotIDfk number
(Unique index on EventDay & TimeSlotIDfk to prevent duplication)


tblEventDateTimeSlot
- EventDateTimeSlotID PK autonumber (optional, but recommended)
- EventIDfk number
- DateTimeSlotIDfk number
(Unique index on EventIDfk & DateTimeSlotIDfk to prevent duplicate
records being created)

tblEventDateTimeSlot contains one record for each event scheduled into
a particular timeslot. Once you have built the tables, you build
forms to populate them with records. Use combo boxes to display lists
of valid values for the user to choose from.








**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Jerome]

Thanks, I'll try that!

The relationship between events and time slots is one-to-many so create a
table with the 10 time slots in it. Now each event record can have a variable
number of time slots assigned to it be using the prmary index of the event
table into the foreign key field of the time slot table.

You've got a bunch of participants who have signed up in another table(?)
mapped to a variable number of events and/or time slots (you weren't too
clear on that)? That would seem to be another one-to-many relationship
between the participants and their events/(or time slots?).

Now, you want to tally # of participants to each event (one-to-many as
seen from the event side), plus you need to tally multiple events to each
participant (one-to-many as seen from the participants side so their printout
can inform them of what events/time slots they've signed up for). This is a
many-to-many relationship between events and participants.

For a many to many relationship, create another table with 2
fields,representing foreign keys for primary index of event table and primary
key of participant table. The primary index of this table will be a composite
primary key defined from both fields. Now each user that signs up for an
event gets his primary key inserted into that table. Use the primary key of
the event table to fill the other column.

This additional table represents # of events for each user AND # of users
for each event. just count up the # records containing whatever event ID
you're interested in or count up # of records with the userID (and resolve
the eventID) to inform the user of what they're signed up for. Use lookup
fields with this table to make things more visually appealing.

Hope that was clear.

Dave

: