Robert:
Firstly I should stress that the UNION operation I described will work
with
your present tables, so while there may be scope for improvement of the
model,
and this would be desirable, it is not an absolute requirement
I'll start at the end with what is meant by Tom's phrase 'Excel on
steroids',
which is one you'll see used from time to time in quite a lot of responses
here. What it means is that an Access table is structured more in the way
we'd build a worksheet in Excel rather than a database in Access. The
main
symptoms of this are:
1. Multiple columns are used for different values of the same type of
attribute. Say you had a table of people and the their MS Office software
usage on a scale of 0 to 10, zero meaning they don't use a particular
piece
of software, 10 meaning they are a heavy user, you might in an Excel
worksheet have a column for the person and then separate columns for Word,
Excel, Access, Outlook, PowerPoint etc. with values from 0 to 10 in the
columns. If you reproduce this with a database table this is known as
'encoding data as column headings'. A fundamental principle of the
database
relational model (the Information Principle) is that data is stored as
values
at row positions in tables and in no other way, so this is not how its
done
in a relational database. Instead you'd have a table for People with one
row
for each, a table for Software with one row for each and a table for
SofwareUsed with columns PersonID, SoftwareID and Usage. So if a person
whose PersonID is 42 uses Access whose SoftwareID is 3 and their usage is
5
on the scale there would be row:
42 3 5
in the SofwareUsed table.
2. Another common symptom of an inappropriately designed table is that it
contains redundant columns and is consequently not properly 'normalized'.
Say in Excel you have a worksheet of contacts with columns ContactName,
AddressLine1, AddressLine2, City, State, ZipCode you may well think that
an
Access table which mirrors this would be fine, but if we look a little
closer
we'll find that this design allows for inconsistent data because it
includes
a redundant State column.
To understand why its redundant we need to look at what normalization
involves. Normalization is a set of formal 'rules' which, if followed,
eliminate redundancy from a table. There are 5 'normal forms', which in
fact
is 6 because an extra one, Boyce Cod Normal Form, was added when it was
found that the original set didn't cope with a particular situation. For
this example we are particularly interested in Third Normal Form (3NF).
Its
formal definition (taken from Chris Date) is as follows:
Third Normal Form: A relvar is in 3NF if and only if its in 2NF and every
non-
key attribute is non-transitively dependent on the primary key.
Very loosely speaking a table is the equivalent of the term relation, a
table
definition is the equivalent of the term relvar (relation variable), a
column
(aka field) is the equivalent of the term attribute, and a row (aka
record)
is the equivalent of the term tuple in the formal language of the
relational
model.
Informally we can translate this into 'all non-key columns must be
functionally dependent solely on the whole of the table's primary key'.
By
'functionally dependent' is meant that the value of one column is
determined
by the value of another, e.g if I'm the person 42 referred to above then
columns Firstname and Lastname are functionally dependent on PersonID
because
for a PersonID value of 42 the values of Firstname and Lastname can only
be
Ken and Sheridan. This is stored only once in the database, so (unless I
change my name back to its original Irish version of Cináed O'Siridean)
anytime I appear in the database I'm always Ken Sheridan.
Lets consider the Excel worksheet an Access table, and in particular the
columns City and State. If John Doe lives in San Francisco the values in
City and State will be San Francisco and California, but we know San
Francisco is in California, so the State column is functionally dependent
on
the City column. The City column is functionally dependent on the
PersonID
column (correctly), so we have a transitive functional dependency
PersonID---
City---->State and the table is not in 3NF. So what? Well lets say we
add
Jane Doe to the table. She also lives in San Francisco but we are
momentarily distracted by a radio report from Dallas and mistakenly put
Texas
in the Sate column. There is nothing to stop us doing this, so we now
have
inconsistent data and when we search for all people in California we don't
get Jane Doe returned and when we search for all people in Texas we
incorrectly get her returned.
To eliminate the redundancy and the risk of such inconsistencies we
'decompose' the table into three tables, people, Cities and States.
States
has just one column State, which is its primary key of course. Cities has
three columns CityID , City and State of which CityID, an arbitrary unique
number such as an autonumber, is the primary key; this is necessary
because
unlike state names, city names can legitimately be duplicated. People
includes a 'foreign key' City ID column (not an autonumber this time)
which
references the primary key of Cities, so we can distinguish between two
cities of the same name. The tables are now in 3NF and here is no
possibility of the sort of inconsistencies we encountered with the
original
table before decomposition.
You might be wondering why, when the Cities table includes a State column
with the State names, we need a States column at all. There are two
reasons:
(a) by enforcing 'referential integrity 'in the relationship between Sates
and Cities we only enter state names in Cities which are present in
States,
so can prevent invalid sate names being entered; (b) if we have only one
city
in a particular state in the Cities table and then delete that row we no
longer have any record of the existence of that state at all; this is
known
as an 'update anomaly'.
I've spent some time boring you with all this stuff about normalization
and
the like, but that's because everything else really stems from it, so its
essential to have a good grasp of what it all means if we are going to
design
solid databases. Getting the 'logical model' i.e. the schema of tables
and
the relationships between them is the key to a reliable and efficient
database, because it is a model of that part of the real world with which
we
are concerned, and if we get the model right the database will behave in
the
same way as the real world, but if we get it wrong we'll end up jumping
through hoops until the cows come hoe to work around the design flaws.
Looking at this in the context of your business model I'll take your
example
of the process of handling an order for 10mdifferent brochures and look
at
this from square one without reference to your existing tables as I think
this will better enable you to look critically at your table structures
and
se if any amendments to the model are required.
Firstly, what entity types do we have? A table models an entity type so
this
determines what tables we need.
Clearly there is an Organizations entity type so we need a table
Organizations. This will have a primary key, which can be a 'natural key'
e.
g. the organization's name if these are all distinct, or a 'surrogate'
key, e.
g. an autonumber. Many people favour the use of surrogate keys even when
a
natural 'candidate key' is available, but in such a situating the
'candidate
key' column should be indexed uniquely as well as creating the autonumber
primary key. Non-key columns will be the attributes of the organizations,
remembering that each must be functionally dependant solely on the key.
One
attribute will be a column to identify the member and other organizations
of
course.
Next we have an entity type Resources (brochures in this case) so we have
a
table for those, again with a primary key and non-key columns representing
other functionally dependant attributes.
I'm not sure when you refer to 'staff name' whether you are referring to
the
member of your own staff handling the order, or on the other hand it
refers
to a member of the organization's staff. The model will be different for
each, so I'll come back to the latter scenario later. For now assuming
its
your own staff then an Employees table is needed with EmployeeID as the
key
(necessary because names can be legitimately duplicated, even within small
workgroups) and columns for their names and other functionally dependent
attributes.
Now we come to an interesting concept, because the relationship between
Organizations and Resources is a many-to-many one, i.e. an organizations
might make one or more orders for resources, and each resource may be
supplied to one or more organizations. Unlike a one-to-many or a
one-to-one
relationship, a many-to-many relationship is not created directly between
two
or more tables; it is always created by a third table which includes
foreign
key columns referencing the primary keys of the tables in the many-to-many
relationship. But on the assumption in the preceding paragraph then
Employees is also a part of the relationship, so its 3-way (ternary) and
an
EmployeeID foreign key is needed. The ternary many-to-many relationship
has
therefore been resolved into three one-to-many relationships.
Relationships
are in fact a special king of entity type, so as well as modelling the
relationship the table also models an entity type (Orders) and this has
attributes of its own, e.g. the date of the order, the quantity ordered
etc,
so this Orders table will have non-key columns for those attributes too.
Lets look now at the alternative scenario where the staff are the
'contacts'
within the organizations i.e. in this context the 'contact' placing the
order
on behalf of their organisation. The difference this makes to the model
is
that the relationship is not now between Organizations, Staff and
Resources,
but between Contacts, Staff and Resources, so the Orders table would now
include a ContactID foreign key column in place of the OrganizationID
foreign
key column. The Contacts table would have a foreign key OrganizationID
column referencing the key of Organizations, so Orders maps to
Organizations
via the two relationships Orders--->Contacts--->Organizations.
The above represents an analysis only of what tables might be appropriate
in
the context of the tasks you've outlines and there would doubtless be
other
tables and relationships involved in the full model.
When it comes to querying the database, the reports you mention can all
easily be created with your existing table, using the UNION query I
described
as the basis. When it comes to distinguishing between members and others
the
DistibutionType column which the query returns, with the constants
'Program
Resource Distribution' and 'Other Organisations Distribution' allows you
to
do this in any queries based on the UNION query or by the WhereCondition
setting of the OpenReport or OpenForm method if opening a report or form
based on the UNION query (such a form would not be updatable of course).
You
could also do it by testing for 'ProgramName IS NULL' or ''ProgramName IS
NOT
NULL' as you say. Nevertheless having two tables is not a good design as
it
encodes data as table names in this case.
When you describe your forms and subforms, which is certainly the type of
interface called for here regardless of the details of the model, you
refer
to 'autopopulating' the subforms. If by this you mean that the subforms
are
showing data from the parent form's underlying table by virtue of being
based
on a query that includes that table then that's fine. but if you mean that
values from the parent form's underlying table are actually being inserted
into the subform's underlying table, then that is almost certainly a very
bad
design as it means the subform's underlying table contains very high
levels
of redundancy and is consequently exposed to the risk of inconsistent
data.
I said 'almost certainly' here because there are situations when to insert
values from a referenced table into a referencing table is valid. The
classic one is that of an InvoiceDetails table which includes a UnitPrice
column even though a Products table to which a foreign key ProductID
column
in InvoiceDetails refers also includes a UnitPrice column. In this case
the
values in the UnitPrice column in Products will change over time, but you
would want those in the UnitPrice column in InvoiceLines to remain static
at
the value when the invoice was raised. In relational-speak what I'm
saying
here is that UnitPrice in Products is functionally dependent on the key of
Products, but UnitPrice in UnitPrice is functionally dependent on the key
of
InvoiceLines.
How far you want to go in recasting your tables is of course for you to
decide, but hopefully, even if you decide not to try and normalize the
tables
fully then the above will have given you a better understanding of where
any
flaws in the design exist and the possible implications of these in terms
of
the integrity of your data.
Ken Sheridan
Stafford, England
Rob said:
Thanks for the comments. Ken, you ask about the business model. I don't
know if this is what you are looking for, but let me try to explain a
little
bit more. We are a nonprofit organization funded by a number of federal
grants. Because we are grant funded we have to track what is going on and
submit the info back to the federal gov't. We are kind of an umbrella
organization whose membership consists of other organizations. Our
primary
purpose is to provide support, training and resources to our member
organizations, but we also provide these same services to non-member
organizations.
In the past we used two separate access db's to track our activities. One
had a table and related form for entry of "technical assistance" (ta)
support to member programs, a table for ta to non-member programs, a table
for resource distributions for member programs, another for resource
distributions to non-member programs and then another table for
"mailings".
The second db was used to track meetings, trainings conducted and
trainings
attended. Staff were very frustrated because they often had to enter
similar data several times in order to get the full picture. For
instance,
someone might receive a phone call from a member program requesting
assistance with a particular issue and during the phone call also request
50
brochures which are sent out later that day. This would require entering
the date, staff person, and name of the organization in three different
screens. Another example was if we had a new brochure come in that is
sent
out to all of our member organizations, the exact same data would have to
be
entered for each.
My goal was to streamline the databases and actually combine them into one
database, making data entry easier and since we are a nonprofit doing all
of
this on my own to avoid increased costs. I am using subforms that pull
info
from the parent form to make data entry easier. For example, when a new
brochure is added and sent out to all of our agencies the parent form
would
ask for the date, the staff name, and the name of the brochure. The child
form then would autopopulate those entries and all that the user had to
enter in the child form was the name of the organization and the quantity
sent. Similarly, if one program ordered 10 different brochures the user
could go to a screen that had a parent form asking for the date, staff
name,
and organization name. The child form would then ask for the names of the
brochures and the quantities of each, again autopopulating the date,
organization name and staff member name. there are a number of places I
have used parent/child forms to make data entry simpler that are similar
to
these examples.
Then at the end of a reporting period the db would generate a report
showing
how many "ta" calls were received, how many brochures were sent out, how
many people were trained, the types of people by profession who attended
trainings, etc. On the original question, we would like to be able to
look
at a couple of different items. First we would like to be able to see
which
member organization is using the most brochures. Secondly, we would also
like to be able to report that we used 640 of x brochure during the last
six
months without paying attention to who actually received them. The union
query was suppose to pull the data from both tables and combine it to show
the total number of each brochure sent out.
If I did use one table as you suggest with a separate form for entering
data
on a member organization and another for non-member organizations, how do
I
get it to set a value that can allow me to look at activity for
non-members
and another for members? Is it a form of an "if" statement? If program
name is null then it must be a non-member, but if program name is not null
then it must be a member organization? Obviously that is laymens
language.
That's the part I couldn't figure out and why I went to two separate
tables,
which is similar to the original db design that someone else did years
ago.
Oh, and yes, the ResourcesID looks up another table to find the names of
the
brochures currently being produced. That table has two columns, an id
column and a title column. I use the following code so that the drop-down
list shows the name rather than the id number. SELECT
[Resources].[ResourcesID], [Resources].[Resource] FROM Resources;
unfortunately I'm not a programmer. I've learned what I know by reading
books on access and browsing the internet. I'm not certain if what I'm
doing is what was described as excel on steroids. Not certain what was
meant by that statement. And yes, i do understand that one should avoid
"date" as a field name.
Robert
As your tables mirror each other you can return a single result table
with
a
[quoted text clipped - 158 lines]
