Simple Design Help Please

[spunkymuffmonkey]

Hi there,

Firstly, thanks for looking at my message and apologies for being so dumb.

I am having problems designing what should be the simplist of db's. I just
can't get my head around what tables I should have and what relationships any
tables might have.

The database I would like is a very simple reservation system for an
internal training session we run.

The session takes place weekly (normally but not exclusively a Wednesday),
so a table for dates would be needed I think.

There are only 8 places available in each session, would this need a table
of its own related to the dates table?

The only data regarding training session candidates needed would be Name and
Tel#, but how would I relate this to the session places?

Although this is very simple I just cannot work out (not for lack of trying)
the tables and relationships I need and any advice or help would be greatly
received.

Thanks again.

 

[Paul Shapiro]

Something like this would be a start:

Person (personID, nameLast, nameFirst, phone)
Primary Key: personID
TrainingSession (trainingSessionID, sessionDateTimeStart, enrollmentLimit,
durationMinutes, title, description ...)
Primary Key: trainingSessionID
PersonTraining (personID, trainingSessionID, wasAbsent, ...)
Primary Key: personID, trainingSessionID

Relationships would be Person -> PersonTraining and TrainingSession ->
PersonTraining

You would have to enforce the enrollment limit in the data entry form since
an Access validation rule cannot reference any other table.

If the training sessions are actually instances of particular courses, you
might have another table for Course or TrainingTopic or whatever would be
the best name. Another relationship from TrainingTopic to TrainingSession,
and TrainingSession would have a foreign key attribute for trainingTopicID
or trainingTopicCode or whatever you used as the TrainingTopic primary key.

 

[BruceM]

Typically in a training database one session can include many (i.e. more
than one) attendee (employee?), and each employee can attend more than one
session, so there is a many-to-many relationship between Employee and
Session, which means a third table is needed to resolve the realtionship.
It may be something like this:

tblEmployee
EmployeeID (primary key, or PK)
FirstName
LastName
etc.

tblSession
SessionID (PK, perhaps autonumber)
Topic
SessionDate
etc.

tblEnrollment
EmployeeID (foreign key, or FK)
SessionID (FK)
' The two fields together can be the PK, or you can add a PK field

Typically there is a pre-existing Employee table. Data are entered into
tblEmployee by way of a separate form.

Make a form based on tblSession, with a subform based on tblEnrollment. On
the subform, a combo box bound to EmployeeID (from tblEnrollment) gets its
Row Source from tblEmployee, so that you select the Employee name but store
the ID. This is accomplished by making EmployeeID the first column in the
Row Source. The second column may be:
LastFirst: [LastName] & ", " & [FirstName]
The column count is 2, the column widths are something like 0";1.5", and the
bound column is 1.

There are other considerations, such as whether the training sessions may be
predetermined programs (MSDS training or whatever), and whether the
instructor is an employee, outside person, or it varies between the two.

The count can be limited to 8 readily enough once the database is put
together. The design is the main consideration at this point.

 

[Maarkr]

not so simple unless it's a one time class - i suppose if you have weekly
training that the subject area would be different or you would have sessions
and nobody would show up more than once. In that case, look up or use the MS
students and classes model:
Members
-MbrID (autonum pk)
-LName, FName
-Phone
Other personal details like dept...
Training
-ClassID (autonum pk)
-ClassDate
-ClassTime
-Location
-Instructor
-Slots
Then you'll need a juction table
TblJunction
-JctID (pk)
-ClassID (long number)
-MbrID (long number)
-Attended (you can use this to track if they showed up or not)
Link the IDs up, then build a class form, a members form then a form based

 

[spunkymuffmonkey]

not so simple unless it's a one time class - i suppose if you have weekly
training that the subject area would be different or you would have sessions
and nobody would show up more than once. In that case, look up or use the MS
students and classes model:
Members
-MbrID (autonum pk)
-LName, FName
-Phone
Other personal details like dept...
Training
-ClassID (autonum pk)
-ClassDate
-ClassTime
-Location
-Instructor
-Slots
Then you'll need a juction table
TblJunction
-JctID (pk)
-ClassID (long number)
-MbrID (long number)
-Attended (you can use this to track if they showed up or not)
Link the IDs up, then build a class form, a members form then a form based

 

[spunkymuffmonkey]

Many thanks for your time and effort when replying to my question!

 

[spunkymuffmonkey]

Many thanks for your time and effort when replying to my question!

Typically in a training database one session can include many (i.e. more
than one) attendee (employee?), and each employee can attend more than one
session, so there is a many-to-many relationship between Employee and
Session, which means a third table is needed to resolve the realtionship.
It may be something like this:

tblEmployee
EmployeeID (primary key, or PK)
FirstName
LastName
etc.

tblSession
SessionID (PK, perhaps autonumber)
Topic
SessionDate
etc.

tblEnrollment
EmployeeID (foreign key, or FK)
SessionID (FK)
' The two fields together can be the PK, or you can add a PK field

Typically there is a pre-existing Employee table. Data are entered into
tblEmployee by way of a separate form.

Make a form based on tblSession, with a subform based on tblEnrollment. On
the subform, a combo box bound to EmployeeID (from tblEnrollment) gets its
Row Source from tblEmployee, so that you select the Employee name but store
the ID. This is accomplished by making EmployeeID the first column in the
Row Source. The second column may be:
LastFirst: [LastName] & ", " & [FirstName]
The column count is 2, the column widths are something like 0";1.5", and the
bound column is 1.

There are other considerations, such as whether the training sessions may be
predetermined programs (MSDS training or whatever), and whether the
instructor is an employee, outside person, or it varies between the two.

The count can be limited to 8 readily enough once the database is put
together. The design is the main consideration at this point.

Hi there,

Firstly, thanks for looking at my message and apologies for being so dumb.

I am having problems designing what should be the simplist of db's. I
just
can't get my head around what tables I should have and what relationships
any
tables might have.

The database I would like is a very simple reservation system for an
internal training session we run.

The session takes place weekly (normally but not exclusively a Wednesday),
so a table for dates would be needed I think.

There are only 8 places available in each session, would this need a table
of its own related to the dates table?

The only data regarding training session candidates needed would be Name
and
Tel#, but how would I relate this to the session places?

Although this is very simple I just cannot work out (not for lack of
trying)
the tables and relationships I need and any advice or help would be
greatly
received.

Thanks again.