Should SQL VIEW always be in 1NF?

Question

SQL VIEW is a global logical table that may or may not be saved. But this is still a table. Therefore, if VIEW always adheres to the first normal form (1NF)? that is, there are no duplicate rows, only scalar types, without ordering from top to bottom or from left to right, etc. What about higher normal forms?

For me, my applications "consume" the results of stored processes, my VIEW queries are "consumed" by SQL queries, and these two uses are mutually exclusive (that is, I do not request the results of sets of stored procedures using SQL and my applications do not contain SQL code). I have seen others use VIEW to "concatenate" multiple values ​​in a column into a single row, usually a comma format. Writing predicates in an SQL query on such a column requires bugs like this:

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

Therefore, it seems reasonable to me to expect that all tables that can be queried consist only of scalar types. Am I too “purist” thinking about this?

Answer 1

It makes sense to make sure that your views are normalized to at least 1NF. Permission duplicates, for example, have the disadvantage that the meaning of the presentation is ambiguous, and the information may be incorrectly identified by users. Incorrect data can occur if tables are updated based on such ambiguities.

EFCodd did not always agree. In his book RM version 2, he suggests allowing keyless views - a big mistake, I think. Codd views do not actually allow duplication, but they allow each column to be nullable and therefore have no keys and are not in 1NF.

A string value containing a comma-delimited list is not in itself a violation of 1NF. A string value is scalar like any other value, regardless of what it contains. Most SQL DBMSs do not allow multi-valued attributes.

Answer 2

Not. I create representations to match the conclusions that my program requires.

Answer 3

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

Not materialized representation is not saved, this is a request.

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

See this answer for more details.

Answer 4

I do not think that this is the rule, but if it were - there was no rule always .

Answer 5

a view (if it is not a materialized / indexed view) is nothing more than a stored query. Views can contain more than one table, can be connected to the same table, etc. Etc.

Answer 6

According to Chris Date, views should be fully normalized:

Choosing which relationships you make basic and which views are arbitrary. As a trivial example, you can have employees, and you can have a basic attitude that contains all employees, and you can have East Coast employees and West Coast employees as two kinds. Or you can have East Coast and West Coast employees as two basic relationships and deduce the union of all of them as a look. This is completely arbitrary.

DBMS Interview with Chris Date - October 1994

Answer 7

No - normalization rules apply to data storage, and not to their presentation. For example, any duplicate lines in a view will break 1NF, which is obviously overly restrictive.

For more information, see First Normal Form .