Jim,

>>Physicly the tables are certainly not normalized, logically they are (because logically the fields are related one to one).
>>
>You remployee name is
>wexample is denormalized and it has nothing to do with redundant fields.

Seems like we've got a different interpretation of the word redundant. By redundant I mean information that is also stored elsewhere. Since the information stored in both the employee and travel table, this information is redundant so I would call these fields redundant fields.

Also note that the values in the fields last_name and first_name for a matching
employee must match also, because they're enforced by constraints. I added this fields because I want to sort the travel table by the name of the employee natively. Following your rules about normalization (at the logical design) this is normalized (or am I missing something) but on the physical layer it's not.

>The rule that is broken is the 3rd Normal form, "All fields are dependent on ONLY the primary key and not on any non-key attribute other than alternate keys." The employee name field in the travle table are dependent on the primary key, yes. But they are also dependend on the emp_id which is not an alternate key for that table. Thus the design is denormalized.

No it's only dependend on the Empid (foreign) key. Note that this has not anything to do with history.

>However, having an Employee and an EmpHistory table which both have an Emp_PayRate field is not denormalized. Emp_payrate in the employee table is dependent on the Emp_id which is the primary key for the table, but Emp_PayRate in the EmpHistory is not dependent on the emp_id field but trahter on the primary key of the EmpHistory table because a given Emp_id may have different rates for different records.

Your approach to solving the history problem has also a name (just looked it up), it's called shadowing.

The problem with this is the word may. So there can be repeated groups. I agree this is a fuzzy area wether or not to call it denormalized. In garrets approach these repeating groups do not occur.

>This si the long wasy of saving that the two Emp_PayRate fields do not have the same domain. The domain for one is "The employee's current pay rate" and the other is "The employee's pay rate at some point in time".

>The design problem that I ahve with the "stacked" approach you suggest has nothing to do with normalization, it has to do with another relational design objective altogether and that is each entity should have a single discrete thing about which it records information. With the stacked approach a single entity is trying to be both current data and historical data at the same time. While with a second history table one is current and the other is historical.

I agree that shadowing can be better in terms of handling RI issues of the current data, but I disagree in having to make a difference between the current and historical data. The current data should be a part of the history.

>Also if you carry the design requirement a little further and discover that there are more than one table that needs to track history of changes, you then need to adapt all the tables that need it for stacked tracking, while with my approach there is still only one history table that simply has to be used by mroe than one other table. Also if the data to be tracked is only poart of the full record, using the same table uses space for all fields instead of just those that require tracking, while my approach is able to be adpated to handle the decision of which field s to track.

Yep, so true, In the shadowed version you would only track these things that can change trough time. It would be a denormalized table if it also tracked non variable data because this data would be depended on only a part of the primary key.

>I am not porporting that my suggested design is the only good one, only that it has advantages over the stacked design. It also has disadvantages as well, there are now two tables to deal with instead of one.

I agree that shadowing is better than stacking because of two things:
- You're able to setup RI for the current record.
- The design is normalized because you omit the static data in the shadowed table.

But it still has disadvantages:
- You've to deal with with two tables, when adressing historical data.
- You cannot setup RI for historical data.

Since this discussion belongs to the other thread I'll continue there.

Walter,