Multiple Values per Column

The multivalued field in a database schema

A table (relation) is in 1NF if:

• There are no duplicated rows in the table
• Each cell is single-valued (no repeating groups or arrays)
• Entries in a column (field) are similar

An anti-pattern that fails to comply with those rules means having a multivalued field in a database schema:

create table tweet
 (
   id      bigint primary key,
   date    timestamptz,
   message text,
   tags    text
 );

Data would then be added with a semicolon separator, for instance, or maybe a pipe | char, or in some cases with a fancy Unicode separator char such as §, ¶, or ¦. Here, we find a classic semicolon:

         id         │ date │ message │          tags          
════════════════════╪══════╪═════════╪════════════════════════
 720553530088669185 │ ...  │ ...     │ #NY17
 720553531665682434 │ ...  │ ...     │ #Endomondo;#endorphins
(2 rows)

Going against 1NF

Using PostgreSQL makes it possible to use the regexp_split_to_array() and regexp_split_to_table() functions we saw earlier, and then to process the data in a relatively same way. The problem with going against 1NF is that it’s nearly impossible to maintain the dataset as the model offers all the database anomalies.