No need to apologise; you were clear in everything you said, just didn't
quite say everything. I had assumed from this that the situation is as
you've now clarified it, but the model I suggested gave the opportunity, with
minimal amendment, to cater for both scenarios, as Clif has pointed out. Of
course, even if you design the database so hat you can record previous as
well as current pastors, it doesn't mean you have to record the previous
ones; you can still record only the current ones if you prefer. The option
to record previous ones will still be there should you decide in future to do
so.
When it comes to what tables you need we need to consider the theoretical
basis of the database relational model to some degree. A reasonable grasp
of the underlying principles does make it a lot easier designing the 'logical
model' i.e. the tables and the relationships between them which go to make up
the database. Getting the logical model correct at the start is the key to a
robust and efficient database as it is a model of the underlying part of the
real world which the database represents. If the logical model doesn't
accurately reflect the underlying reality, then developing a smoothly
functioning application is going to give a lot of headaches, if it does then
developing the interface will be pretty straightforward on the whole.
Before looking at your specific situation it might help if I give you a
couple of my stock replies, firstly on the relational model. You might like
to print this off so you can consider it at leisure:
"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 thee 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."
and secondly on normalization (this does duplicate some of the above):
"Normalization is the process of eliminating redundancy from a database, and
involves decomposing a table into several related tables. In a relational
database each table represents an entity type, e.g. Contacts, Companies,
Cities, States etc. and each column in a table represents an attribute type
of the entity type, e.g. ContactID, FirstName and LastName might be attribute
types of Contacts and hence columns of a Contacts table. Its important that
each attribute type must be specific to the entity type, so that each 'fact'
is stored once only. In the jargon its said that the attribute type is
'functionally dependent' solely on the whole of the primary key of a table.
To relate tables a 'referencing' table will have a foreign key column which
makes the link to the 'referenced' table, e.g. a Contacts table might have a
CompanyID column as a foreign key, while a Companies table has a CompanyID
column as its primary key. Consequently no data other than the CompanyID
needs to be stored in a row in the Contacts table for all the company
information for that contact to be known; its available via the relationship
and can be returned in a query joining the two tables on the CompanyID
columns.
Similarly the Companies table might have a CityID column and the Cities
table a StateID column. If its an international database the States (or more
generically Regions) table would have a CountryID referencing the primary key
of a Countries table. So via the relationships, simply by entering (in
reality this would be selected from a list of Companies in a combo box, not
typed in)a CompanyID in the Contacts table the location of the contact's
company is also known. Redundancy, and therefore repetitive data entry is
avoided.
To see how a database is made up of related tables take a look at the sample
Northwind database. Its not completely normalized in fact (deliberately so
for the sake of simplicity) but illustrates the main principles of how tables
representing entity types relate to each other. An example of its lack of
proper normalization can be found in its 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 'update anomalies', 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.
An example of what at first sight might seem to be redundancy, but in fact
is not, can also be found in Northwind. The Products table and the
OrderDetails table both have UnitPrice columns. It might be thought that the
unit price of a product could always be looked up from the Products table, so
its unnecessary in Order Details. However, the unit price of a product will
change over time, but each order needs to retain the price in force at the
time the order was created. Consequently a UnitPrice column is needed in
both tables; that in products holds the current price and is used to get the
value for that in Order Details (code in the ProductID control's AfterUpdate
event procedure in the Order Details Subform does this), which then remains
static when the current price (in products) changes. In each case UnitPrice
is functionally dependent on the key of the table, so there is no redundancy"
OK, if you've managed to plough through that and stay awake, lets consider
your scenario in the context of these underlying principles. I can't really
say to what degree the advice you've been given on the need for specific
tables is sound or not. I'd need to know a lot more details of the
underlying 'reality' which the database needs to model. But keep in you mind
that each table represents an entity type and each column represents an
attribute type of the entity type in question. Each attribute type must be
specific to the entity type (this is where normalization comes in as
described above). In the jargon its said that each non-key column must be
'functionally dependent' on the whole of the primary key of the table, or as
John Vinson once said 'The key, the whole key and nothing but the key, so
help me Codd', which is a nice way of remembering what makes a table
normalized to Third Normal Form (3NF).
So, Churches is obviously an entity type and things like the name and
location of the church are its attribute types, as is it planter, the last
represented by a PlanterID foreign key column.
When it comes to people connected to the church in some way or other things
get a little more complex, but not frighteningly so, as the principles
involved are quite simple. All people share certain attribute types of
course; we all have names, an address, a date of birth and so on. So there
is an entity type People with these common attribute types, and a table can
represent this attribute type. People with different roles may well have
attribute types which are specific to their role, e.g. a pastor is likely to
have attribute types which a member of his congregation would not have. If I
can use an example from the C of E an attribute type for a C of E priest
might be Date of Ordination. So the entity type Pastors is a sub-type of the
entity type People. The way a sub-type is modelled in a relational data base
is by means of a one-to-one relationship. In the case of People and its
sub-type Pastors this would mean that there would be a table people with a
numeric primary key PersonID (don't use names as a primary key, they can be
duplicated), and a Pastors table also with a numeric primary key PersonID
(you can call it PastorID if you wish, but I prefer to keep the column names
the same). In the case of the Pastors table PersonID would also be a foreign
key referencing the primary key of People. The people table would have
columns for the common attributes like names and address etc., the Pastors
table would have columns only for those attribute type specific to the
Pastors entity type, e.g. Date of Ordination, but not the common attributes
like names and address.
A sub-type can of course have sub-types of its own; Chris Date in one of his
books gives the example of a type Employees with sub-type programmers, and
sub-types of programmers, System programmers and Application programmers.
As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an autonumber
PersonID column in the People table, but the primary key of Pastors or other
sub-type of people must be a straightforward long integer number data type,
not an autonumber.
When it comes to events connected with a church you need to think carefully
about how these fit into the model. One thing you need to be careful of is
deciding whether an event type is a sub-type of another type, or whether the
type of an event is an attribute type of a single entity type. You might
have a topmost Events entity type with attribute type such as Event Date,
Location etc and a Baptisms sub-type with attribute types such as ChildID,
FatherID, MotherID, all three referencing the People table. Another way of
modelling it would be to have a single Events table with an attribute type
EventType, in which a value 'Baptism' would be entered. The problem with the
latter approach of course is that if you also have ChildID, FatherID,
MotherID columns these won't be appropriate to other types of events in the
same table. In this case therefore a type/sub-type model is probably the
most appropriate. In the case of other entity types, however, the second
approach might be more suitable. To take an extreme example, in a personnel
database you would not have an Employees table and a separate table for every
Job Title in the organization; you'd be more likely to have a Positions table
and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo
columns to model the many-to-many relationship between Employees and
positions.
By all means attend a class, but I'm also a greet believer in the value of
the printed word, so here's another of my stock replies, this time on Access
books:
"Of the general purpose primers on Access I particularly like John L
Viescas's 'Running Microsoft Access' (Microsoft Press).
For an introduction to VBA programming in Access Evan Callahan's 'Microsoft
Access/Visual Basic Step by Step' (Microsoft Press) is easy to follow and,
while not taking things to a very high level, provides a solid basis on which
to build.
At a more advanced level the 'Access Developer's Handbook' by Paul
Litwin,Ken Getz and Mike Gunderloy (Sybex) covers the subject in great
detail, and contains a vast amount of usable code.
A useful and easy to read little book on the theoretical basis of the
database relational model is Mark Whitehorn and Bill Marklyn's 'Inside
Relational Databases With Examples in Access' (Springer).
For a highly authoritative but quite abstract explanation of the relational
model Chris Date's 'An Introduction to Database Systems' (Addison Wesley) has
for many years been regarded as a definitive work on the subject. Its by no
means an easy read, however.
For SQL Joe Celko's 'SQL for Smarties' (Morgan Kaufmann) is a wealth of
information on how to write queries. It deals with standard SQL, however,
and is not Access oriented. In fact Joe's views on Access do not bear
repetition where they might be read by people of a sensitive disposition.
Even so it is worth its weight in gold."
Ken Sheridan
Stafford, England
