Hi! As I'm still quite new to Access I wouldn't be surprised if this is a very stupid question that I should be able to find out by myself. Anyways, here goes: I'm putting up a database in Access, and I've been trying to keep my tables in compliance with the First, Second and Third Normal Forms. My question is concerning the Third Normal Form.
One of the tables in my database contains information on employees. The Primary Key is the "Employer ID", and the other attributes are "Name", "Phone", "Role" and "Last updated". I have thought it over and have sort of come to the conclusion that this "Last updated" attribute is not in compliance with the Third Normal Form, as it is not only dependable on "Employer ID", but also the other attributes. Is this correct?
If I am right, the solution that springs to mind is to make a new table with "Last updated" as the primary key and use this as a foreign key in the Employer table. I would also use the same foreign key should I want to keep track of when other tables were last updated. Would this be a solution?
All this being said, I guess somebody more experienced with Access could accomplish this by more elegant means, but as I'm a newbie I'd like to keep things as easy as possible.
I would not consider that "Last updated" is dependent on other non-key columns. If we go back to Codd's definition of the third normal form:
1. The table is in second normal form.
2. Every column that is not part of the primary key nor can be considered as a candidate key (i.e. could not be a primary key or a part thereof) is directly dependent of the primary key (i.e. it cannot be derived from another column).
Depending on the business plan of your system (more precisely on why you need this piece of information and what you intend to do with it), you might consider that "Last updated" is a direct attribute of the primary key "Employer ID", and its contents obviously cannot be derived (computed) from any other column.
For several reasons, I would not store such a value into the table, but few are directly related to the third normal form. Among them:
1. In real situations, "Last updated" is seldom a genuine attribute of "Employer ID": it provides information on the database and its history, not on the data it contains (there are exceptions, though). I would admit "Creation Date" more easily.
2. This kind of information is often useless (there are exceptions too): you know when a row was modified, but not how (what data changed), nor why and by whom.
3. In Access, such information is not reliable: nothing prevents from opening the table and modify the contents of a row without updating "Last updated", and the same is true for an UPDATE query. The accuracy of the information relies on some piece of code in the inferface (Form(s)). If there are several places from where the table can be updated, the accuracy of "Last updated" can be difficult to garantee, specially if the program is subject to many modifications over a long period of time.