Try thinking in terms of the real world entity types that are involved and
how these relate to each other rather than directly in terms of the tables
which model these. Once you have arrived at a correct visualisation of this
'logical model' implementing it as related tables is straightforward, with
each entity type being represented by a table with the attribute types of the
entity type being represented by columns, e.g. an entity type People would
have attributes such as PersonID, FirstName, LastName DateOfBirth etc.
A relationship type between tables which represent two entity types related
one-to-many can be directly between then by means of a foreign key in the
referencing (many side) table which references the primary key of the
referenced (one side) table. You might for instance have an Addresses table
with a PersonID foreign key and attributes such as Building, Street,
AddressType (Home, Business etc) etc.
The above example assumes, however, that each address relates to only one
person, which is unlikely to be true in most cases. Consequently there is
really a many-to-many relationship between People and Addresses. A
many-to-many relationship is modelled by a table which references the primary
keys of the two tables representing the entity type in the many-to-many
relationship, so you might have a table PeopleAddresses with columns PersonID
and AddressID. While this table models a relationship type it also models an
entity type as a relationship type is just a special kind of entity type, so
it can have attributes of its own, and hence columns representing these. So
the AddressType column would be in PeopleAddresses not in Addresses, as would
any other columns representing attributes of a the relationship type such as
DateFrom and DateTo.
With a table modelling a many-to-many relationship the relationship is
resolved into two one-to-many relationships so these are the relationships
you create in the relationships window. The relationships are thus like
this, with the < and > signs representing the 'many' sides of the two
relationships, i.e the referencing table:
People----<PeopleAddresses>----Addresses
Applying these principles to your scenario we start with the following
entity types:
1. Customers
2. Locations
3. Items
4. Leaflets
Appointments are a relationship type between the first two of these three
entity types in that a customer will have an appointment at a location, so
the Appointments table would have columns referencing the primary keys of
Customers and Locations, along with other columns representing attributes of
the relationship type (which, remember is also an entity type), e.g.
CustomerID
LocationID
AppontmentDateTime
Do not use separate columns for the date and time as in Access there can
only be a date/time value not date or time values. Date/time values are in
fact stored as a 64 bit floating point number as an offset from 30 December
1899 00:00:00, so when you enter a time you are actually entering a time on
that date, but seeing just the time. Similarly when you enter a date you are
entering a date/time value at midnight at the start of the day.
As regards Visits, you could regard Visits and Appointments as the same
entity type and use a single table. If an appointment does not result in a
visit for any reason the columns representing the attributes of visits would
be left Null. I'd advise against this, however, as Nulls are ambiguous. I'd
go for a Visits table as you have done, so that a row is only inserted into
that table if and when an appointment results in a visit. In the jargon
Visits can be regarded as a 'sub-type' of Appointments. The relationship
here would be one-to-one from Appointments to Visits, or more strictly
speaking one-to-(one or zero). A one-to-one relationship is characterized by
the fact that the primary key of the referencing table (Visits) is also a
foreign key referencing the primary key of the referenced table
(Appointments). This raises the question what is the primary key of
Appointments? A 'candidate key' would be the combination of the CustomerID,
LocationID and AppontmentDateTime columns, so you could have these columns as
the composite primary key of both Appointments and Visits, and relate the
tables on the three columns. You can simplify things, however, by adding an
autonumber AppointmentID to Appointments as its primary key, and a
(non-autonumber) VisitID as the primary key of Visits and relate the tables
on these columns.
When it comes to Items and Leaflets there is a many-to-many relationship
between each of these and Visits, so as well as Items and Leaflets tables you
need VisitItems and VisitLeaflets tables to represent the two relationship
types, the former with columns VisitID, ItemID, Item etc., the latter with
VisitID, ItemID, Leaflet etc.
Lets try and map the model out diagrammatically. Firstly we have the
relationship of customers to a locations, i.e. the appointments:
Customers---<Appointments>----Locations
Then we have those from Appointments to Visits to Items/Leaflets.
Appointments-----Visits----<VisitItems>----Items
and
Appointments-----Visits----<VisitLeaflets>----Leaflets
You'll also have a many-to-many relationship from visits to employees (the
people carrying out the visits). Even if its normal to only have one person
visit a customer I'd suggest modelling a many-to-many relationship so that if
an occasion should arise where more than one employee carries out the visit
this will be catered for in the logical model:
Appointments-----Visits----<VisitEmployees>----Employees
Although a so-called 'junction' or table like this is mostly used to model
many-to-many relationship types it can just as easily model a one-to-many
relationship type, but leaves the option open. Sometimes it is used even
where there is no possibility of a many-to-many relationship; this is where
only a proportion of the rows in the referenced table are referenced by rows
in the referencing table and is done to avoid the use of Null foreign keys,
which are semantically ambiguous.
Ken Sheridan
Stafford, England
