It all boils down to what are known in relational-database-speak as
'functional dependencies'. A well designed relational database is made up of
a number of tables, each of which represents an entity type, e.g. Employees,
Customers, Products, Orders etc. Each column of a table represents an
attribute type of the entity type, e.g. FirstName, LastName, and DateHired
are attribute types of Employees.
An important difference between a relational database and a spreadsheet is
that a database should contain no redundancy, i.e. we should not be told the
same 'fact' more than once, e.g. that London is in the UK or that New York is
in the USA, whether this is in separate rows of one table or in separate
tables. Otherwise inconsistencies become possible; if we had both a City and
Country column in a table of addresses one row might have London in the UK,
another in the USA or anywhere else for that matter. If there is only one
row in one table where we are told that London is in the UK then wherever
London is referred to anywhere in the database we know its in the UK.
Ignoring regional definitions like states and counties for the sake of this
example the place where we are told its in the UK, and that New York is in
the USA is in the Country column of a Cities table with one row per city.
This table is related to a table of addresses on CityID columns in each, the
primary key of the Cities table and a foreign key in the Addresses table. In
the jargon the Country column is said to be dependent solely on the whole of
the primary key of the Cities table. If there was a Country column in the
Addresses table then it would be functionally dependent on the foreign key
CityID column, not on the primary key of the Addresses table, so the
Addresses table would be said to be not properly normalized. Normalization
is the process of eliminating such redundancies and goes from First Normal
Form (1NF) to Fifth Normal Form (5NF), plus some rather bizarre higher normal
forms. Each normal form has a set of rules based on functional dependencies
to determine if a table is normalized to that level.
If you are still awake after reading all the above boring theoretical stuff
lets see how the concept of functional dependency applies in your case.
In your Personnel table there will be one row for Mary Jones and one for
Bill Smith, which is fine as there is no redundancy; we are told all their
attributes just the once.
In your Accounts table you might have a column AccountHolder referencing the
primary key, e.g. EmployeeID (a unique number as names can be duplicated), of
the Personnel table. This is fine so long as you only want to record who the
current account holder is.
In a Transactions table you might have an AccountCode column referencing the
primary key of the Accounts table. As you've spotted, if an account is
passed from Mary to Bill then all previous transaction records will now show
Bill as the account holder, which is incorrect in terms of the transaction as
the fact that Mary was the original account holder at the time of the
transaction remains true for ever more. The solution is to have an
AccountHolder column in Transactions as well as in the Accounts table. When
a transaction record is created the current AccountHolder value from Accounts
is assigned to the AccountHolder column in Transactions, where it will remain
unchanged, whoever becomes the account holder. Having an AccountHolder
column in Transactions is not redundant, unlike having Country in Addresses,
as it is functionally dependent solely on the whole of the key of
Transactions, i.e. its determined by who was in place at the time of the
transaction, not by who is the current account holder.
You'll find an example of this sort of thing in a slightly different but
analogous context, in the Order Details Subform in the sample Northwind
database, where a unit price is assigned to the current record by looking it
up from the Products table in the AfterUpdate event procedure of the
ProductID control.
A slightly different model, which John hinted at in his reply, is where
you'd want to store not just who is the current account holder, but everybody
who has been account holder over time. You might think that by knowing who
the account holder was for each transaction in effect tells you this, but
what if there are no transactions during somebody's (short lived) tenure as
account holder? You'd then never know that they were an account holder. In
this scenario you'd have another table AccountHolders which would have two
foreign key columns referencing the primary keys of personnel and Accounts
and DateFrom and DateTo columns; this table actually models a many-to-many
relationship type between accounts and personnel, resolving it into two
one-to-many relationships. You'd no longer need an AccountHolder column in
Transactions, as the account holder is whoever holds it when the transaction
date falls between their DateFrom and DateTo values, the relationship between
Transactions and AccountHolders thus being a slightly more complex one than
the usual case of two values matching.
Ken Sheridan
Stafford, England
