Hi Jamie,

Technically, normalisation applies only to the logical relation, not to a
physical table or any view of it. As I would hope you know, any
visualisation of the data conained within a table, is a view; and it's
implemented as a view behind the scenes. Views are not virtual tables, they
are visualisations of datasets. If you open a "table" to look at its data,
you're visualising that data through an additional layer called a view, not
the table itself, and certainly not the relation. You can never "see" a
table's data directly, because a table is just the physical implementation
of a structured storage mechanism. Whether I choose to view a slice of my
data horizontally, vertically, or include joins or calculated fields, the
rules of normalisation applied to the relation(s) remain(s) unchanged,
because I have not changed the relation(s). But to create a calculated field
in a relation would indeed constitute a breach of normalisation rules.

Strictly speaking, materialised views can be normalised or un-normalised,
and that's OK, because they are not the primary data storage relations. They
are simply views that have been materialised for purposes other than data
storage. They're equivalent to temp tables, so the rules don't apply.

Just as an aside; in a later post you state that we should always aim for
the highest normal form. That's not entirely accurate or desirable. I'm sure
that when you think about it, you'll agree. DKNF or (heaven forbit) Lossless
Joins are not for the feint of heart; nor are they anywhere near practical
in 99.99% of cases. Even in data warehouses, we rarely go beyond 4NF.

You're correct when you quote, "Strict rules modified in practice". A
classic example is financial data models, where storing current balances in
tables are the norm. The relations are designed that way, thus lowering
normal form.

In any case, you have to make the distinction between (a) the storage of
data, and (b) the use of that data. Pure storage requires normalisation. Use
of the data requires whatever form is necessary to convert that data into
information or knowlege. Therefore, denormalising views by including
calculated fields is OK, because it is an output function, not a data
storage function. Data modelling 101.

I think those who are very strict about normalization in base tables
should be very strict about normalization in virtual tables.
No. One is not the other.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


"Jamie Collins" wrote in message
oups.com...
On 15 Aug, 22:05, fredg wrote:
I created a table with two fields named "projectestimate" and
"inspectionfee".

The inspection fee is 5% of the project estimate. I want the
"inspectionfee"
field to automically calculate the amount.

How do I do that?

In a table? You don't.
Access tables are for storing data, not for data manipulation or
display.

you can do the calculation directly on a form or report, using an
unbound text control:
= [ProjectEstimate] * [InspectionRate]

the resulting value should not be saved in any table.

In this simple case I agree: there can be no good reason for storing
the result.

If you store the [ProjectEstimate] and [InspectionRate] it's easy
enough to calculate the actual fee in a Query:
InspectionFee:[ProjectEstimate] * [InspectionRate]

Not sure I agree anymore. Did you mean to say 'query' (lowercase)?

If you saved your query with the calculated column as a stored Query
(Title Case) object then this would become a virtual table. You would
seem to be saying that denormalization in a base table is wrong but
fine in a virtual table. That sounds contradictory.

For me, this is one of the differences between a VIEW and a PROCEDURE
i.e. the resultset of a PROC doesn't need to be normalized. The
trouble is, how do you get the Access/Jet engine to see your PROC as a
PROC e.g. this in Northwind (ANSI-92 Query Mode SQL syntax):

CREATE PROCEDURE GetOrderProcessIntervals
AS
SELECT OrderID, OrderDate, ShippedDate,
DATEDIFF('D', OrderDate, ShippedDate) AS order_process_interval_days
FROM Orders;

actually creates a VIEW! i.e. although it can be EXECUTED:

EXECUTE GetOrderProcessIntervals;

it can also be queried as a table:

SELECT *
FROM GetOrderProcessIntervals;

and we now have a denormalized table.

The only workaround I can think of is to supply a useless parameter
value e.g.

CREATE PROCEDURE GetOrderProcessIntervals
(
this_not_used INTEGER = 0
)
AS
SELECT OrderID, OrderDate, ShippedDate,
DATEDIFF('D', OrderDate, ShippedDate) AS order_process_interval_days
FROM Orders;

Now

SELECT *
FROM GetOrderProcessIntervals;

fails with a 'missing parameter' error.

So the choice seems to be to ask users to ignore daft parameter or be
a little less strict on normalization rules.

Jamie.

--