Arlen:
Firstly, before addressing your specific points, here's a brief summary of
the relational model, as its important to have an understanding of the
theoretical basis if your are going to design reliable database applications.
"The database relational model was originally proposed by E F Codd in a
paper in 1970 in the journal 'Communications of the Association for Computing
Machinery'. Since then there has been a vast amount of further theoretical
work, and the relational model has shown itself to be a robust one. Without
going too deeply into the theoretical basis, which can be quite abstract, a
relational database in essence models a part of the real world in terms of
its entity types and the relationship types between them. Note the inclusion
of the word 'type' in both cases here. While its almost always used in the
former case, its often omitted in the latter case. This is a little bit
sloppy but not too important. When one talks about a 'relationship' it
really refers to a relationship value. As an example 'marriage' is a
relationship type, but my being married to my wife Fiona is a relationship
value, represented by our names on the marriage certificate, which is the
physical equivalent of a row in a Marriages table with columns Husband and
Wife, each referencing the primary key of a table People. This is a
many-to-many relationship type (I've been married twice so would be in two
rows, my first wife would also be in two rows as she remarried too). It is
resolved into two one-to-many relationship types, People to Marriages in each
case, in one case via the Husband column in the other via the Wife column.
In a relational database tables model Entity Types. In the above example
People is an entity type, modelled by the People table. Marriage is also an
entity type, modelled by the Marriages table. As we've seen its also a
relationship type. In fact a relationship type is just a special kind of
entity type.
Each column in a table represents an attribute type of each entity type, so
attribute types of People might be FirstName, LastName, DateOfBirth etc.
This table would also have a PersonID numeric column (usually an autonumber)
as its primary key as names are not unique. Each row in a table represents
one instance of the entity type, and the attributes of each instance are
represented by values at column positions in the row. This is the only way
that data can be legitimately stored in a relational database.
Its important that there is no redundancy in the information content of the
database. This is achieved by the process of 'normalization'. Normalization
is based on a set of 'normal form's ranging from First Normal Form (1NF) to
Fifth Normal Form (5NF). There is also one called Boyce/Codd Normal Form
(BCNF) which was inserted when it was found that the original Third Normal
Form was deficient; it didn't cater satisfactorily for tables with two or
more candidate keys where the keys were composite and overlapped, i.e. had a
column in common. I won't go into the details of normalization here; you'll
find it written up in plenty of places.
To see an example of redundancy and therefore a table which is not properly
normalized take a look at the Customers table in the sample Northwind
database which comes with Access. You'll see that it includes City, Region
and Country columns. If you look at its data you'll se that we are
redundantly told that São Paulo is in province SP which is in country Brazil
4 times. This is not just inefficient, it is dangerous as it leaves the
table open to inconsistent data being entered. There is nothing to stop
somebody putting São Paulo in the UK, USA or in each in separate rows in the
table for instance. To normalize the table it should be decomposed into
Customers, Cities, Regions and Countries tables, each of the first three with
a foreign key referencing the primary key of the next table up in the
hierarchy."
Applying this to your scenario you have entity types Teachers, Rooms and
KeyTypes
Teachers will have attributes, and thus columns, such as FirstName,
LastName, etc. As names can be duplicated it should also have as its primary
key a unique TeacherID column. An autonumber will be fine for this.
Rooms will have an attribute RoomNumber as its primary key. It could also
have other attributes such as Floor, Building etc if necessary.
KeyTypes will have an attribute KeyType (Cabinet, Desk, Door etc) as its
primary key. This will most probably be its only column. Note that this
table represents types of keys not the individual keys per se.
One thing you haven't said is whether a room can have more than one teacher
as well as a teacher having more than one room. In the former case the
relationship type would be many-to-many, in the latter one-to-many. I'll
deal with the latter firt as this is the simplest scenario:
All that is needed to model the one-to-many relationship type is a foreign
key TeacherID column in the Rooms table.
With a many-to-many relationship this is modelled by a separate table,
TeacherRooms say, with two foreign key columns, TeacherID and RoomNumber.
These two columns form the table's composite primary key. So if a teacher
has 3 rooms there would be 3 rows in this table with the same TeacherID and
different RoomNumber values. Conversely if a room has 2 teachers there
wouild be 2 rows with the same RoomMunber value and different TeacherID
values.
The relationship type between Rooms and KeyTypes is many-to-many, so a
separate table RoomKeys is needed with columns RoomNumber and KeyType. What
other columns this table has depends on how you want to record the keys per
room. One option would be to have a NumberOfKeys column, so if a room had 4
desk keys there would be a row with the room number, 'Desk' in the KeyType
column and 4 in the NumberOfKeys column. With this solution the primary key
of this table would be a composite one of RoomNumber and KeyType.
The other possible solution is to have a separate row in this table for each
individual key for the room, with a column to identify the key, e.g. KeyName
or whatever suits. In this case the primary key of this table would be a
composite one of RoomNumber, KeyType and KeyName.
For data entry a suitable solution would be a Teachers form, bound to the
Teachers table, or better still a query on the table sorted by LastName,
FisrtName so that the records are ordered in the form. This form should be
in single form view.
Within the teachers form you'd have a rooms subform, linked to the parent
table on TeacherID. This would be base either on the Rooms table, or the
TeacherRooms table if the relationship is many-to-many.
You'd also have a subform based on the RoomKeys table. Here, however, you
hit a snag. You'd want to show the rooms and keys as continuous list in the
subforms, but you cannot have a form is continuous form view as subform
within another form in continuous form view. So you cannot have a keys
subform within a continuous rooms subform.
There is a solution, however, which is to use 'correlated' subforms. To do
this you'd create a form based on the Rooms or TeacherRooms table depending
on what the relationship type is. Note that with the former you would
normally have allocated the rooms to teachers first(via a separate Rooms
form), so the subform will show the rooms for the current teacher. It is
possible, however, to add rows to the Rooms table via the subform, which will
automatically allocate them to the current teacher. To prevent the same room
being allocated to more than one teacher its important that a unique index be
created on the RoomNumber and TeacherID columns in the Rooms table.
If the relationship type is many-to-many then the subform would be based on
the TeacherRooms table. In this case there would be no TeacherID column in
the Rooms table of course.
In both possible scenarios its not necessary to include a control bound to
the TeacherID column in the subform. In the first scenario you'd have text
box bound to the RoomNumber column, in the second a combo box bound to the
RoomNumber column, with a RowSource property of:
SELECT RoomNumber FROM Rooms ORDER BY RoomNumber;
You can then pick a number from the drop down list.
You'd the create a continuous view form based on the RoomKeys table and
embed this in the Teachers form as a subform. This however would be linked
in a different way to correlate it with the rooms subform. Before you can do
this, though, you need to add an unbound text box txtRoomNumber to the parent
Teachers form, set its Visible property to False (No) to hide it, and set its
ControlSource property so that it references the current room number in the
rooms subform, e.g.
=sfcRooms.Form.RoomNumber
where sfcRooms is the name of the subform control on the Teachers form which
houses the rooms subform, not the name of its underlying form object (unless
both have the same name of course).
With this unbound control you now link the keys subform by setting its
LinkMasterFields property to the name of the hidden text box on the parent
form, i.e. txtRoomNumber, rather than to the name of a field in the parent
form's underlying recordset as you'll have done with the rooms subform. The
LinkChildFields property is set to RoomNumber in the normal way.
The way it will work is that when you move to a teacher record in the parent
form the rooms subform will show the rooms for the current teacher. As you
move from row to row in the rooms subform the keys subform will show the keys
for the current room. You can enter new keys in the subform which would have
a combo box bound to the KeyType column with a RowSource of:
SELECT KeyType FROM keyTypes ORDER BY KeyType;
You'd have other controls in the keys subform for NumberOfKeys or KeyName
(or whatever) depending on how you decide to record the keys per room.
If you want to create report then its simpler as you don't need to use
subreports (although you could). You simply join the Teachers Rooms (or
TeacherRooms), RoomKeys and KeyTypes tables in a query, base a report on the
query and group it first by LastName, then TeacherID, then by RoomNumber.
Put the teacher data in a TeacherID group header (don't show the TeacherID,
just the names etc; the TeacherID is just to uniquely identify each teacher
behind the scenes), the room data in a RomNumber group header and the keys
data in the detail section. The report wizard can set it up for you once
you've created the query.
The above addresses your question as posed, but as has been suggested
elsewhere in this thread the model might be more complex. You might prefer
to have a Locations table rather than a Rooms table to handle keys not
related to rooms per se, with a primary key column such as LocationID rather
than RoomNumber. The relationship type between teachers, rooms and keys
might be more complex if a room can have more than one teacher and each
teacher is responsible for a subset of the keys for that room. For this
you'd need a Keys table with one row per physical key and there would be a
ternary (3-way) relationship type between Teachers, Rooms and Keys modelled
by a table with three foreign key columns referencing the primary keys of the
three 'referenced' tables. You should think these things out carefully
before creating any tables, setting out the entity types and the
relationships between them on paper as an 'entity relationship diagram', with
boxes for each entity type and directional lines showing the relationships.
Only when you are completely satisfied that the model represents the reality
should you start putting the database together.
Ken Sheridan
Stafford, England
