Re: Computed column?

Chuck Bayes wrote:
>
> I'm kinda new to the DBA world, but I would think that while populating a
> calculated field from fields within the same table does violate 3rdNormal,

Not if A and B make up the primary key.

> the
> benefit in terms of performance out weighs strictly adhering to normalization.
>
> Your thoughts? Anybody else?
>

Hmm. While column C certainly "depends" on columns A and B, would it really be violating the "spirit" of the definition of 3nf? Certainly normalizing it out to a separate table would be sheer folly.

Before even getting to any normalization considerations, however, I would eliminate the calculated field as unnecessary. Sure, there would be a slight performance benefit. Whether or not it outweighs other considerations would be a subject for analysis. For the sake of maintainability and robustness, I would tend strongly toward a view such as the following and hang the ever-so-slight performance hit.

  create view calc_table as
    select a, b, (a + b) / 10 c
    from original_table;

Suppose later we decide to change the equation to something like "((a + b) + .5) / 10" to, say, correct for round off error. Or add another column D with a value according to an entirely different equation. Sure, we could go through and make the changes to the table -- or we could simply recreate the view.

The view is "clean", the stored values are "messy". Which would you rather maintain?

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"