SQL VIEW 应该总是在 1NF 中吗? [英] Should a SQL VIEW always be in 1NF?

问题描述

SQL VIEW 是一个全局逻辑表，可能会或可能不会被持久化.但它仍然是一张桌子.因此，VIEW 是否应该始终遵循第一范式 (1NF)?即没有重复的行，只有标量类型，没有从上到下或从左到右的排序等.更高范式呢?

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?

对我来说，我的应用程序消耗"存储过程的结果，我的视图被 SQL 查询消耗"，这两种用法是相互排斥的(即我不使用 SQL 查询存储过程的结果集，我的应用程序不包含 SQL 代码).我见过其他人使用 VIEW 将列中的多个值连接"成一行，通常是逗号分隔的格式.在针对此类列的 SQL 查询中编写谓词需要类似于以下内容的代码:

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?

推荐答案

确保您的视图至少标准化为 1NF 是非常有意义的.例如，允许重复的缺点是视图的含义不明确，用户可能会错误识别信息.如果基于此类歧义更新表格，则可能会出现不正确的数据.

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 不一定同意.在他的 RM 版本 2 书中，他建议允许没有键的视图 - 我认为这是一个大错误.Codd 的视图实际上不允许重复，但它们允许每一列都可以为空，因此没有键，也不在 1NF 中.

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.

包含逗号分隔列表的字符串值本身并不违反 1NF.字符串值是一个标量，就像任何其他值一样，无论它包含什么.大多数 SQL DBMS 不允许多值属性.

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.

这篇关于SQL VIEW 应该总是在 1NF 中吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！