To add to what Jeff wrote, think about what it is you want to know. Like,
what questions could you ask (or be asked by a supervisor or outside
oversight). From there you can continue building on the "entities" as Jeff
put it.
Once you start adding up your entities, if you begin thinking about whether
that entity will always have data or if it is possible that nothing would be
filled in, you can also start splitting out your data to be in additional
tables. (Called normalization.)
I could think of another entity, if it is to be tracked, like date of first
training, or a need for refresher, or tracking refresher training. Tracking
the date of someone taking the exact same training (I.e., if it has not been
modified) would allow you to ask the questions, 1) when did they first take
the training? 2) Are the due for retraining/refresher (say every x years or
months), 3) how many times have they attended training?
If however, your training gets updated and you keep track of that as well,
you could keep track of a training version in addition to the date that it is
offered. This would allow review to determine who has/hasn't seen a
particular portion of the training (the portion that is different/added).
One important thing to think about when you have a loose idea of your
tables, is to see, what about each "row" (record) in this table makes it
different or unique? Is it possible that two records could have the same
exact information? By possible, I don't mean by physically typing in the
information, but that when the database is it possible that it is filled with
duplicate real world information. If so then there are a few things you can
do to make them unique. One is to use an autonumber, another is to add
something else into the table that really would make it unique (an autonumber
does that assuming that you don't reset the autonumber/force the autonumber
to duplicate an existing number.)
Where Jeff talks about a third table, this is something of a cross-link
table, it contains data from each of the other two tables and gives you the
link between the two groups. With a relationship from the name of the
individual in the third table with the name field of the first table and a
relationship between the date in the third table with the date field in the
first table you can tell when the person has gone to training and on what
date(s), and you can also figure out who is has gone to training either on a
specific date, or in a span of dates.
Obviously though you wouldn't want the same person to sign up for the class
more than once on a single date. (Do think about how you handle/obtain the
name data though, what if an administrative assistant calls and says I need
to have space for 6 people that are not yet identified and how you handle
that.) If it is true that the name of one person can be signed up for a
class only once on that date, then if in the third table you make Name and
Date your primary key, an error will occur when the same named individual is
signed up for the same class twice on the same date.
If on the other hand (and just for educational purposes), the person could
attend the class only one time, then you would have your table of names, and
table of dates, then you could have a third table (in order to maintain
normalized tables) that again has just name and date, but instead of making
the primary key both fields, you make the primary key the name. That way the
person could show up only once in the records.
So you're thinking, why not add the date to the name table? Well, if this
is not a class that is a prerequisite for employment, then a new employee
would be added to the database, and at some time in the future would be
trained. During the time that the employee was added and receiving/being
scheduled for the training, the date field would be blank/null. This breaks
the normalization of the table.
Think of normalization as a clean desk/home. Everything has a place and
everything is in it's place. And somewhat the reverse, don't make a place
for something that will never be there, or only sometimes will be there. At
the same time, don't make so many tables that have related information that
you won't be able to keep track of the information.
Hope that helps a little more.
Jeff Boyce said:
To work out the relationships, work out the entities first. What "things"
are you looking to keep information about?
Clearly, you have persons (who will sign up for training). That sounds like
a first table.
And since you describe a single course/training topic, you wouldn't need
"courses" (unless, of course, your training regime might expand in the
future). But, as you've described, you have instances of that single
course, on different dates. That (instance-on-date) seems like a second
table.
So it seems like, based on your description so far, you'll need a way to
track persons-signed-up-for-course-on-specific-date, which would be a third
table.
If this is a reasonable description of your situation, then you need to NOW
describe the relationships. For example, could you have one person sign up
for more than one instance? If so, [Person] and [Instance] have a
one-to-many relationship.
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
spunkymuffmonkey said:
Hi there,
Firstly, thanks for looking at my message and apologies for such a basic
question.
I am having problems designing what seems 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 weekly training session we run, the training course teaches the
same thing weekly, only the candidates change. The session takes place
weekly (normally but not exclusively a Wednesday), so a table for dates
would
be needed I think. The course is the same week There are a maximum of 10
places (altho 8 is the norm) available in each and some candidates attend
more than one (i.e. refresher training!) 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,
Position 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 relationship, I have tried a few permiatations but quickly
get in a muddle without the desired result. Some advice or help would be
greatly received.
Thanks again.
