Column Depending on Others

0
votes

I have the following table:

                         

My gut tells me it's bad because TOTAL can be deduced from TIME_IN and TIME_OUT.

Of what normal form is this a breach?

1
databaserelational-databasenormalizationdatabase-normalization

1 Answers

1
votes

Storing values that are the result of calculations usually violates 3NF. They constitute a transitive dependency.

In your case, the transitive dependency is 

time_in, time_out -> total

The pair {time_in, time_out} is not a candidate key.

Normally (ahem), you'd resolve transitive dependencies by projection, but that doesn't usually make sense when the transitive dependency involves the results of calculations. There are three obvious options.

  • Drop the column "total", and calculate it in each query.
  • Drop the column "total", and build a view (or a materialized view) that calculates the total.
  • Keep the column "total" and add a CHECK() constraint to guarantee the value is always right.

Each of these might have different effects on performance and maintenance.