Databases with SQLite

First Normal Form - 1NF

The first normalization step consists of 3 conditions:

• Rows in a table should not have any meaningful or inherent order. The value in one row cannot depend on the value of any neighbouring rows.
  Your books table does not have to be in alphabetical (or ANY order). That's what the order by clause is for.
  You don't buy books in alphabetical order, so why would you enter them in the database that way?


• Every row must be unique. One way to achieve this is by providing a Primary Key of one or more columns for each table.
  


• Every column holds 1 and only 1 atomic value - one that cannot be broken down further.

Let's take a look at a 'books' table using my digital books database:

book title | pubyear | format <=== colunm headings


Book 1|1985,2001|epub <=== WRONG: 2 pubyear values

The Third Book|2021|pdf,epub <=== WRONG: 2 format values

Book the Ninth|2020|azw <=== WRONG: missing unique id

This first attempt seems to make sense, but it contradicts some conditions.

To correct this add each book and their format or pubyear separately, and add a unique ID.

id|book|pubyear|format

1|The Third Book|2021|epub

2|Book 1|1985|epub

3|Book the Ninth|2020|azw

4|Book 1|2001|epub

5|The Third Book|2021|pdf

The way to ensure each line has a unique ID is done during the creation stage using 'integer primary key autoincrement'.

↟ Second Normal Form