Editing Database normalization (section)

=== Satisfying 6NF ===
A simple and intuitive definition of the [[sixth normal form]] is that ''"a table is in [[Sixth normal form|6NF]] when '''the row contains the Primary Key, and at most one other attribute"'''''<nowiki/>'''.'''<ref>{{Cite web|url=https://stackoverflow.com/questions/4824714/would-like-to-understand-6nf-with-an-example|title=normalization - Would like to Understand 6NF with an Example|website=Stack Overflow|access-date=2019-01-23}}</ref>

That means, for example, the '''Publisher''' table designed while [[#Satisfying_1NF|creating the 1NF]]:

{| class="wikitable"
|+Publisher
!Publisher ID
!Name
!Country
|-
|1
|Apress
|USA
|}

needs to be further decomposed into two tables:

{|
|
{| class="wikitable"
|+Publisher
!Publisher ID
!Name
|-
|1
|Apress
|}
|
{| class="wikitable"
|+Publisher country
!Publisher ID
!Country
|-
|1
|USA
|}
 |}

The obvious drawback of 6NF is the proliferation of tables required to represent the information on a single entity. If a table in 5NF has one primary key column and N attributes, representing the same information in 6NF will require N tables; multi-field updates to a single conceptual record will require updates to multiple tables; and inserts and deletes will similarly require operations across multiple tables. For this reason, in databases intended to serve [[online transaction processing]] (OLTP) needs, 6NF should not be used.

However, in [[data warehouses]], which do not permit interactive updates and which are specialized for fast query on large data volumes, certain DBMSs use an internal 6NF representation – known as a [[Column-oriented DBMS|columnar data store]]. In situations where the number of unique values of a column is far less than the number of rows in the table, column-oriented storage allow significant savings in space through data compression. Columnar storage also allows fast execution of range queries (e.g., show all records where a particular column is between X and Y, or less than X.)

In all these cases, however, the database designer does not have to perform 6NF normalization manually by creating separate tables. Some DBMSs that are specialized for warehousing, such as [[Sybase IQ]], use columnar storage by default, but the designer still sees only a single multi-column table. Other DBMSs, such as Microsoft SQL Server 2012 and later, let you specify a "columnstore index" for a particular table.<ref>Microsoft Corporation. Columnstore Indexes: Overview. https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview . Accessed March 23, 2020.</ref>