new database ... how to best design it!?

[Xeronimo]

Hi there,

I've created an online form where people (or rather school classes and
youth groups) can register for an event we're organizing. They can
indicate *5 workshops* that they're interested in and they can choose
*two date combinations* that suit them best for visiting us. The data is
saved into one table on my SQL server.

Now I need to import these registrations into an Access (2003) program
to then manage and plan them.

I guess the inscription needs to be important in three steps:

1) the data concerning *the group* who's interested in coming
2) the data concerning *the person in charge* (might come with different
groups at different times)
3) the data concerning *the workshops* they'd like to participate in and
the times that suit them best

I've already created the first two steps and they work.

The problem is how to best import the choices they've made and how to
manage/plan them ...

The event goes for 2 days (with 3 time slots in the morning and 3 in the
afternoon). There are about 60 workshops.

Each group will be attributed 3 workshops (at least 1 of those they've
indicated during their inscription and 2 others, but always 3 in total)
on a first come, first served basis.

The problem is that I'm not sure about how to start now ... I would need
a kind of a database grid with 12 fields per workshop (12 time slots in
total over the 2 days) where I would then fill in the adequate groups
(via a form displaying the imported inscription/group info) until
they're all full?

Then, based on this grid, I could create reports as to who come where
and when, and also print confirmations for the groups, listing which
workshops they'll participate it.

This probably sounds very confusing ... but any help is greatly
appreciated!!!

ps. I'm not the one who planned this workshop event! I'm simply told to
now create a management program for it ...

 

[Fred]

I noticed that nobody answered. I know that you made a valiant attempt at
dsribing you situation, but you hopped all over the place, got the car before
the horse, and left out important information.

Start by figuring out what the real world entities are that you want to
database, and then design a table structure to store them. Or tell us those
so the we can help. In that description don't even use words like (ike
"Form" Grid" "I want to" "fill in" which doing only harm to your
description.

For the portion that's in the SQL server, your would ideally design this
table structure in the SQL server, and then just import the tables as is.
If somebody has already designed it, than you will have to tell this forum
what that table structure is (which you haven't so that we can help you
either utilizes their good work, or help you either work around their bad
work.

Hope that helps a little.

 

[Xeronimo]

Hi Fred,

Thanks for answering then let me try to explain it again.

The event we need to manage is a science festival which will go for two
days. There will be 60 workshops. Each of the workshop will have 12
sessions (3 each in the AM and PM of day 1, and the same on day 2).

In order to participate the groups need to perform an online
inscription. They need to enter the details of the group and of the
person in charge as well as the workshops they'd be interested in and
the time slot they would like to use.

This data then needs to be imported (or linked to) an Access front end
so our people can manage and plan.

Out of the 5 workshops the groups have selected they will get at least
one and then two others (for a total of 3 activities, corresponding to
one time slot of 3 sessions).

So what we need is a system to attribute 3 workshops to each group (but
of course not 'double book' them!).

Regarding the tables:

Right now there is one table in an SQL server daba base which will
contain the online inscriptions. The inscription page already works fine.

Then I've also already created an empty table for the 'person in charge'
data, linked via a one-to-many relationship to a table for the 'group'
data. Both would of course needed to be linked to the actual
'inscription' details (their choices).

Ok, I'll stop for now, otherwise I'll be accused of hopping around again

Thanks a lot!

Jerome

 

[Fred]

Hello Jerome,

Sounds like a cool event. Also a complicated thing for your valiant effort
to communicate to us.

Here's are a few thought and questions to start sorting through this. I
think that dealing with these will be an important part of your progress on
this help you help yourself as well as help us to help you.

I'm assuming that attending a "workshop" means attending one session of a
workshop? I.e. that a "workshop" will be repeating the same thing 12 times?
For now I'm assuming yes.

Is it ALWAYS 12 times? E.G. never 11, and never running 24 (2 sets) of
sessions with the same content. For now, I'm assuming yes




From a data architecture standpoint, there are two or three meansings for
the same word " Workshop" floating around here (bad idea) let's try to look
at that and name them

1. WorkshopCurriculum The content of a workshop session

2. "WorkshopSession" Instance of of running a WorkshopCurriculum for one
session

3. A TwelveSessionBlockOfWorkshopSessions. I suspect that you do not
need to database this entity, but you must realizes that yo have been
(probably mistakenly) talking about this as if it is an entity to be dealt
with.

I don't know what "inscription" would men in this context. I assuming
that it is the act or results of somebody signing up?

- - -

How can thee be a choice of "date combinations" in a two day event. "Date
Combination" implies at least two dates, and there is only one possibility of
such in a 2 day event.

- - - -

Are all of the signups ("inscriptions") really in just one table? (I think
that that would be a big "failure to normalize") Can you give us an idea of
the structure of it in your SQL server ? If it has relevant structural
problems that will make your life difficult, willl you fix them or will you
work around them?

Again, I wouldn't ask all of these questions unless I thought that the
porcess will also be directly useful to yourself.

 

[Xeronimo]

Hello Fred,

Thanks for answering!

I'm assuming that attending a "workshop" means attending one session of a
workshop? I.e. that a "workshop" will be repeating the same thing 12 times?
For now I'm assuming yes.
Yes!

Is it ALWAYS 12 times? E.G. never 11, and never running 24 (2 sets) of
sessions with the same content. For now, I'm assuming yes
Yes!

From a data architecture standpoint, there are two or three meansings for
the same word " Workshop" floating around here (bad idea) let's try to look
at that and name them

1. WorkshopCurriculum The content of a workshop session

2. "WorkshopSession" Instance of of running a WorkshopCurriculum for one
session

3. A TwelveSessionBlockOfWorkshopSessions. I suspect that you do not
need to database this entity, but you must realizes that yo have been
(probably mistakenly) talking about this as if it is an entity to be dealt
with.

A fitting summary.

I don't know what "inscription" would men in this context. I assuming
that it is the act or results of somebody signing up?

It was a bad translation from French What I meant wasthe act of
signing up. Groups can sign up online and indicate 5 WorkshopCurriculums
they're interested in. They will get one of those at least and two
others to make it 3 in total (so each group will fill in 3 time slots
overall, in 3 different WorkshopSessions)

How can thee be a choice of "date combinations" in a two day event. "Date
Combination" implies at least two dates, and there is only one possibility of
such in a 2 day event.

The days are divided into AM and PM! The group could say: we can come
during the morning of day one ( = 3 time slots) or on the afternoon of
day two ( = 3 time slots).

Are all of the signups ("inscriptions") really in just one table? (I think
that that would be a big "failure to normalize") Can you give us an idea of
the structure of it in your SQL server ? If it has relevant structural
problems that will make your life difficult, willl you fix them or will you
work around them?

The whole online form gets saved into ONE table on my SQL Server
(tInscriptions). I've planned (actually I am updating an older Access
program ...) to import the data and split it into 3 tables
(tOrganisation, tResponsable, tDemande). A user has to do this by
clicking on the respective buttons (and check if the organisation
doesn't exist already via a sub-form) and then linking the tDemande data
to the tOrganisation data (since those IDs only exist AFTER the import
into the respective tables).

I know this isn't very elegant but it works well enough for us ... I'm
more worried about the planning and management part! You must be
horrified

Again, I wouldn't ask all of these questions unless I thought that the
porcess will also be directly useful to yourself.

Don't worry, thanks a lot for you help!

Jerome

 

[Hans Up]

Groups can sign up online and indicate 5 WorkshopCurriculums
they're interested in. They will get one of those at least and two
others to make it 3 in total (so each group will fill in 3 time slots
overall, in 3 different WorkshopSessions)

How do you determine whether a WorkshopSession is full?

Is it one group per WorkshopSession?

Or are there a fixed number of seats available for each WorkshopSession
(e.g. 10)? Say group #1, which consists of 3 members, has been
scheduled for a WorkshopSession --- are there 7 seats still available
for that session?

You said you will schedule sessions on a first come first served basis.
How do you determine which group's request is the first? Since no
WorkshopSessions have been scheduled at that point, does the first group
get all their top 3 WorkshopSession choices?

This thing may be very complex, Jerome. Maybe it seems less so to you
because you already thought through the details. But from an outsider's
viewpoint, it all seems vague.

Good luck,
Hans

 

[Xeronimo]

Hello Hans,

Thanks for replying! Rest assured though, I'm aware that it isn't easy

How do you determine whether a WorkshopSession is full?

One group per WorkshopSession

Is it one group per WorkshopSession?
Yes

You said you will schedule sessions on a first come first served basis.
How do you determine which group's request is the first? Since no
WorkshopSessions have been scheduled at that point, does the first group
get all their top 3 WorkshopSession choices?

We go by the date that they've signed up for the event.

The scheduling of which group will attend which WSession will be managed
manually. So that hasn't to be automated.

This thing may be very complex, Jerome. Maybe it seems less so to you
because you already thought through the details. But from an outsider's
viewpoint, it all seems vague.

I can totally imagine that! I hoped my description would be somewhat
explanatory

Thanks anyway for your help!

Jerome

 

[Fred]

Jerome,

You still didn't tell us your SQL table structure. But I think that I can
guess the answer to the important one which how are recording those 5
WorkshopCurriculum requests. Also how are recording the "person in charge".
You said that it could be different people for different WorkshopSessions ,
yet at that point, WorkshopSessions don't yet exist. Is it tied to the
groups time periods?

You can't really say that mis-designed table "works" because the only thing
that it has accomplished is recording their requests, and even then in a
format that is hostile to accomplishing the real work on this. Is your
import a one-time en masses (there's a word we borrowed from French) event.
If so there's hope. Just normalize it as you import it.

- - - -

Next, what is your misison for this DB? Clearly, it includes recording
your scheduling of groups into workshop sessions. For this you will
certainly need a list of groups, which I'm guessing is what constitutes are
record in yoru SQL db. If your mission does NOT include automated
comparison, of the scheduling to their requests, then maybee you can live
with not-normalizing their session request data.

Here's my gut feel on a structure to record your scheduling (again, shorten
my long explanatory names).


Table: Groups One record for each signed-up group
PK = Group_ID
I'm guessing that this can be easily obtained from your SQL table.


Table: WorkShopCurriculums
PK = WorkshopCurriculum_ID

Table: Periods One record for each period (has 12 records total)
PK = Period_ID

Table: InstancesOfSchedulingOfAGroupIntoAPeriod
Field: Group_ID
Field: Period_ID
Field: WorkshopCurriculum_ID
PK may not be needed, but I'd make one:
InstancesOfSchedulingOfAGroupIntoAPeriod_ID

That should support everything that you want to do on the scheduling side.

Hope that helps a little

 

[Xeronimo]

Thanks Fred, I'll consider these points.

I'm slowly crawling forward ...