Databases with SQLite

Datatypes

Before we can begin designing our tables, we need to be aware of what types of data SQLite can handle.

There are several datatypes available in other DB systems, but SQLite uses only the following:

• text
  Character data. May be Unicode or UTF8
• blob
  binary large object. Any kind of data (.jpg, .pdf, .zip, .flac) stored as is
• integer
  Positive or negative whole numbers. +2(⁶³⁾-1 to -2(⁶³)
  +9223372036854775807 to -9223372036854775808
• real
  Real numbers with decimal values
• null
  Missing data. NOT an empty string or 0

Having said that, SQLite allows dynamic typing rather than the static or rigid typing, which most (all?) other database engines use.

You may read about storage classes, and affinity. They are important to know about, but not crucial at this point.

Storage classes are a kind of meta-container for datatypes. Affinity is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required.

A good description and explanation of storage classes, datatypes, and affinity: Datatypes In SQLite

For our purposes of creating and using a database of our digital book collection, we really only need to concern ourselves with text and numeric types.