Carlo:
The relationship type between Adults and Children is really many-to-many as
each adult can have one or more child and each child has two parents, along
with other relatives such as grandparents, aunts or uncles who might be the
carer depositing them at the crèche.
In a relational database a many-to-many relationship is modelled by a third
table with two foreign key columns (fields), each referencing the primary key
of one of the other two tables. So in this case you'd have a table Adults
with primary key AdultID say, and a table Children with primary key ChildID.
In both cases the keys can conveniently be autonumbers. The relationship
between the two is modelled by a third table, Custodianship say, with columns
AdultID and ChildID and probably a Relationship column with values such as
Father, Mother, Grandmother etc. In this table the AdultID and ChildID
columns are not autonumbers, however, but straightforward long integer number
data type. This table has a composite primary key of AdultID and ChildID.
So if Adult 43 (the children's mother) brings in children 123 and 124 the
table would have rows:
AdultID ChildID Relationship
43 123 Mother
43 124 Mother
If the children's father, Adult 99, also brings them two more rows would be
added:
99 123 Father
99 124 Father
You'd also have a Relationships table, which is simply a list of all
possible relationships, Father, Mother etc in a single column, defined as the
primary key. This allows referential integrity to be enforced in its
relationship with Custodianship so that only valid relationship values can be
entered in that table.
For data entry the usual approach would be to have a form, in single form
view, based on the Adults table and within this a subform, in continuous form
view based on the Custodianship table, linked on the AdultID columns. This
table would have two controls, both combo boxes, one bound to ChildID, the
other to Relationship. The ChildID combo box would be set up as follows so
that you can select a child by name, but the underlying value will be the
numeric ChildID:
ControlSource: ChildID
RowSource: SELECT ChildID, FirstName & " " & LastName FROM Children
ORDER BY LastName, FirstName;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
The Relationship combo box would be set up as follows:
ControlSource: Relationship
RowSource: SELECT Relationship FROM Relationships ORDER BY Relationship;
Its other properties can be left as the defaults.
If space on the form is a problem then the subform can be placed on a
separate page of a tab control on the form.
To log the use of the centre you can have another table, Attendances say,
for this with the date, type of training (probably referencing yet another
table of training activities) etc. The question now arises as to what you
relate this table to in order to record who is attending. If its merely to
record the adult attending, and you infer from this that they are accompanied
by the children you've already assigned to them (a risky assumption I'd say),
then you simply need a foreign key AdultID referencing the key of Adults. If
it’s the adult and the actual accompanying children being logged then you
again have a many to many relationship, this time between Attendances and
Custodianship, so another table is necessary, AttendancePeople say. As
before this will have foreign keys referencing the primary keys of each
referenced table. In the case of Attendances this can be an autonumber,
AttendanceID, so the foreign key in AttendancePeople would be a long integer
number AtendanceID. In the case of Custodianship, though, remember that this
has a composite primary key for AdultID and ChildID, so the foreign key in
AttendancePeople would also be a composite one of AdultID and ChildID. For
data entry in a form combo boxes can be used, set up as described above.
As you see you've now got a framework of related tables. This is the
'logical model' or 'schema' and is really just a model of that part of the
real world with which you are concerned in terms of its entity types
(modelled by tables) and the relationship types between them (in some cases
modelled by a foreign key, in others, where the relationship type is
many-to-many, by a third table which resolves the is many-to-many
relationship type into two one-to-many relationship types). You'll have
probably found it difficult to get a mental picture of this 'logical model'
from my description, but if you draw it out diagrammatically on paper with
boxes representing each table and directional line representing the
relationships between them (what's called an entity relationship diagram) you
should get a better idea of how it all fits together, particularly if you
supplement this with some background reading as Armen suggested.
You will find that you'll need other tables too, e.g. a Cities table for use
in recording addresses, which in turn might reference a Regions or States
table. The important thing is that each 'entity type' is modelled by its own
table, which is then referenced where necessary. This means that each 'fact'
is recorded only once in the database, eliminating redundancy. The process
of eliminating redundancy by 'decomposing' a table into more than one table
is known as normalization.
An example of its lack of proper normalization can be found in the sample
Northwind database's Customers table. You'll see that this has City, Region
and Country columns so we are told numerous times that São Paulo is in SP
region (as is Resende) and that SP region is in Brazil. Not only does this
require repetitive data entry, but more importantly it opens up the risk of
inconsistent data, e.g. it would be perfectly possible to put São Paulo in
California in one row and California in Ireland! Proper normalization as I
described above would prevent this as the fact that São Paulo is in SP region
would be stored only once in the database as would the fact that SP region is
in Brazil and California is in the USA.
Ken Sheridan
Stafford, England
