Hi, Brandie.
First an “academic†definition of the most important “Normal Formsâ€,
paraphrased from Litwin, Getz, and Gilbert’s “Access 97 Developer’s Handbookâ€:
First Normal Form
Table Features:
- Describe one entity
- Have no duplicate rows; i.e., each has a primary key
- Columns and rows are unordered
- Column values are atomic, i.e., consist of a single value not a list of
values
Second Normal Form
- All tables are in 1st NF
- Every non-key column is dependent on the primary key
That is, each table stores data relating to one “thing†(e.g., Customers,
Orders, etc.) and the thing is fully described by its entire primary key.
Frequently, non-2NF tables are resolved by decomposing them into 2 tables,
and brought back together using a query.
Third Normal Form
- All tables are in 2nd NF
- All non-key columns are mutually independent
For example, a frequent error of new users is storing a calculated field.
For example, if you have Qty and PerItemCost fields, you could store a
TotalCost (equal to Qty*PerItemCost), but it would then not be in 3NF.
Again, the way to resolve this is with a query which calculates this total
“on the flyâ€.
There are higher order normal forms of diminishing value.
On a more practical level, when I’m designing a data base, I think first in
terms of “thingsâ€â€”Customers, Projects, etc. I give each table a primary key,
which I and many other developers prefer to be a single AutoNumber key. Then
I flesh out the attributes that describe each thing, which become
fields—CustomerName, Address, City, State, etc.
The only field that should be duplicated in a second table is another
table’s primary key, called a “foreign†key in the second table. For
example, an Orders table would contain a CustomerID field to identify the
customer, but not the CustomerName, Address, etc. fields. Those attributes
are already fully and correctly described in Customers table record that has
that CustomerID.
You can think of this foreign key has “opening†or giving you “Access†to
all of the fields in the Customers table via a query that links the two
tables into a single recordset for printing in a report, displaying on a
form, etc.
Next, I think in terms of relationships between the "things". There are
three—one-to-one, one-to-many, and many-to-many. Your two projects tables
(the one that has funding information and the one with Notes, Concept, and
Strategy) are in a one-to-one relationship. Each describe attributes of a
single project.
When you have a one-to-one relationship, 99% of the time, you can simply add
the fields from one into the other table, since they simply describe other
attributes of the same “thingâ€. Cases for splitting this information into
two tables generally relate to the need for different security level access
to different fields--to give, say, only Human Resources access to an
employee's Salary or Health information, while enabling everyone to see an
employee's name, picture, extension, title, etc.
One to many relationships are the most common—many orders for a single
customer, many order line items for a single order, etc. These are
implemented by including the primary key of the One side as a foreign key in
the many side.
Many-to-many relationships (many customers can order the same product, many
products can be purchased by the same customer) cannot be implemented
directly, but rather by two one-to-many relationships.
Because you have the ProjectName in each table and for other reasons, your
application is currently not normalized. To correct this, I see the
following tables:
Customers
CustomerID AutoNumber Primary Key (PK)
CustomerName Text
Address Text
City Text
State Text
…other Customer attributes
Projects
ProjectNumber AutoNumber or Number (PK)
ProjectName Text
Funding Text
Notes Text or Memo
Concept Text or Memo
Strategy Text or Memo
Because there is a many-to-many relationship between Customers and Projects,
you need an intermediate table to represent it, which is in a one-to-many
relationship with both Customers and Projects.
ProjectCustomers
ProjectCustomerID Autonumber (PK)
ProjectNumber Number (Foreign Key to Projects)
CustomerID Number (Foreign Key to Customers)
So you could have a main form based on Projects which showed its main data,
and a subform based on a query joining ProjectCustomers to Customers, in
order to show the CustomerName, phone number, etc. on the subform.
For the contacts, its implementation differs depending on whether you want
to show any contact with a given customer, or to show contact for a given
customer on a given project.
To show any contact, Customers and Contacts would be in a one-to-many
relationship, and so can be implemented with a main form based on Customers
with an embedded continuous subform based on Contacts, linked on the
CustomerID.
Contacts
ContactID AutoNumber (PK)
CustomerID Number (FK to Customers)
ContactDate Date/Time
ContactTime Date/Time
Notes Text or Memo
If you want to associate the contact with the customer AND the project, then
Contacts is the many side of a one-to-many relationship with
ProjectCustomers. This is a similar relationship to the
Customer->Orders->OrderItems relationship in the sample Northwind database
that can be accessed via the Help menu. One way to implement the Contacts
would be by a main form based on a query joining the ProjectCustomers,
Projects, and Customers tables, with a continuous subform based on Contacts:
Contacts
ContactID AutoNumber (PK)
ProjCustID Number (FK to ProjectCustomers)
ContactDate Date/Time
ContactTime Date/Time
Notes Text or Memo
I hope that helps.
Sprinks
