On 17 Aug, 11:12, "Graham R Seach" wrote:
Technically, normalisation applies only to the logical relation, not to a
physical table or any view of it.

We need a conceptual layer. The one I am employing is SQL (the
language)...

Views are not virtual tables

In SQL terms, that's a misstatement because a SQL VIEW is a virtual
table.

If you open a "table" to look at its data,
you're visualising that data through an additional layer called a view

Again, in SQL terms, that's a misstatement because a SQL VIEW is a
table (lowercase) but a SQL TABLE is not a SQL VIEW.

FWIW my source of reference is the SQL-92 specification.

As I would hope you know, any
visualisation of the data conained within a table, is a view

In your entire post, if you substituted your term 'view' for my term
'resultset' then we would be in broad agreement. As it is, you seem to
use 'view' to mean 'SQL VIEW' and 'resultset' interchangeably, which I
find more than a little confusing to be honest.

In application design I often write a vanilla SQL query and think to
myself, "I'm sure this would be useful in other situations" and would
therefore look to create a persisted object in my SQL DBMS. But how to
decide whether to expose it as a SQL VIEW or a SQL PROCEDURE? Often
the choice of PROCEDURE is a no-brainer (contains control of flow
procedural code, aggregated results make no sense without delimiting
parameter values, etc) but it only become a VIEW if I can achieve the
same degree of normalization as a comparable base table. I simply do
not apply normalization to a PROCEDURE's resultset.

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.

Yes, I put it badly. I'm not sure I even believe in 6NF g.

Even in data warehouses, we rarely go beyond 4NF.

I think you are mistaken. I was taught that 5NF is always achievable
and that data warehouses are often purposely denormalized. A single
column lookup table is in 5NF and that's a very common design in OLTP!

Jamie.

--