Should a SQL VIEW always be in 1NF?

Question

A SQL VIEW is a global, logical table that may or may not be persisted. But it's still a table. Therefore, should a VIEW always adhere to first normal form (1NF)? i.e. no duplicate rows, scalar types only, no top-to-bottom or left-to-right ordering, etc. What about the higher normal forms?

For me, my applications 'consume' the results of stored procs, my VIEWs are 'consumed' by SQL queries, and these two usages are mutually exclusive (i.e. I don’t query the resultsets of stored procs using SQL and my applications do not contain SQL code). I've seen others use a VIEW to 'concatenate' multiple values in a column into a single row, usually comma-separated format. Writing predicates in a SQL query against such a column requires a kludges similar to this:

',' + concat_col + ',' LIKE '%' + ',' + search_value + ',' + '%'

So it seems to me reasonable to expect all tables that can be queried to consist of only scalar types. Am I being too 'purist' by thinking this?

Answer 1

No - I create views to match the output that my program requires.

Answer 2

The whole point of relational systems is that you keep data in normalized relations for efficiency and / or manageability, and then use the relational operators to convert them into the relations you need.

A non-materialized view is not stored, it's a query.

That's why you should create it in the form that best fits your applications needs.

See this answer for more detail.

Answer 3

It makes perfect sense to ensure your views are normalized to at least 1NF. Permitting duplicates for example has the disadvantage that the meaning of the view is made ambiguous and information may be misidentified by users. Incorrect data could occur if tables are updated based on such ambiguities.

E.F.Codd didn't necessarily agree though. In his RM version 2 book he proposes allowing views without keys - a big mistake I think. Codd's views don't actually permit duplicates but they do allow every column to be nullable and therefore don't have keys and aren't in 1NF.

A string value containing a comma-delimitted list is not itself a violation of 1NF. A string value is a scalar like any other value, whatever it contains. Most SQL DBMSs don't permit multi-valued attributes.