table design - calculated field

[Guest]

I have a field in my Zip Code table called Current_Vendor_Count. This value
is a calculation - it is calculated on a form. Is there a way I can store
the calculation at the table level? Am I wrong to want to even do this? Are
values that are calculated on the fly in a form, generally not stored in the
within the table? The following is the calculation:

Current_Vendor_Count = DCount("*", "tbl_Vendor", "(tbl_Zip_Code.Zip_Code =
tbl_Vendor.P_Zip_Code) AND (tbl_Zip_Code.Local_Agency_Code =
tbl_Vendor.Local_Agency_Code) AND [Status_Code] = 'Active' AND
[Peer_Group_Code] <> 11 ")

 

[Larry Daugherty]

Yes, storing calculated values in the database is not a good idea. In
the first place it violates a Relational Rule. That's just a way of
quoting Authority. There are very good reasons why you shouldn't.
You calculate in one place but the result would then go into a table.
That table can be viewed and massaged from more than one place If you
can see it you can change it. You can see the elements which were
elements in the calculation they can be changed without performing the
calculation. Sooner or later, trouble arises silently. You get the
idea.

The practice is to perform the calculation any time you need to
display the result.It requires significantly less storage to manage
the code for the calculation than it does to grow every record in your
table(s) by storing the results. Even Access databases can get into
the millions of records.

HTH

 

[Jamie Collins]

The practice is to perform the calculation any time you need to
display the result.It requires significantly less storage to manage
the code for the calculation than it does to grow every record in your
table(s) by storing the results.

I think the measure should be "when the cost of the calculation is
higher than the cost of a simple read":

http://www.dbazine.com/ofinterest/oi-articles/celko4

it violates a Relational Rule. That's just a way of
quoting Authority.

I'll quote another:

"You are not supposed to put a calculated column in a table in a pure
SQL database. And as the guardian of pure SQL, I should oppose this
practice. Too bad the real world is not as nice as the theoretical
world."

Jamie.

--