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

I don't think this is a rule, but if it was - No rule should always be followed.

Answer 4

a view (unless it is materialized/indexed view) is nothing but a stored query Views can contain more than one table, can have self joins to the same table etc etc

Answer 5

No - normalization rules apply to the persistence of data, not the presentation of it. E.g., any duplicate rows in a view would break 1NF, which is obviously overly restrictive.

For more info, see First normal form.