--- title: 'Data Types' source: 'https://academia.sh/en/courses/relational-theory/data-types' course: 'Data Modeling and Relational Theory' language: en updated: '2026-08-23T07:00:48+00:00' license: 'CC BY-SA 4.0' --- # Data Types The choice among integer, decimal, and floating-point numbers; text length and collation; storing dates alongside a time zone; binary data, truth values, and enumeration patterns. The previous five lessons built the schema: relations, keys, constraints, and the logic of null values. What a column **holds** was passed over without justification — dates were written as text, amounts as integers. This lesson's question is: by what criteria is a column's type chosen, and where does the cost of the wrong choice show up? A type choice determines three things at once: which values can be stored, how comparison and sorting are carried out, and how many bytes are spent per row. The first two affect correctness, the third affects cost; the wrong choice is usually noticed through the first two. ## Numbers and Money The How Computers Work course established that a floating-point number works with binary fractions and that most decimal fractions cannot be represented exactly. That fact holds in a database too, and it does not show up on screen: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box SELECT 0.1 + 0.2 AS sum_result, 0.1 + 0.2 = 0.3 AS point_three_equal, 10 + 20 = 30 AS cents_equal; SQL ``` ``` ┌────────────┬───────────────────┬─────────────┐ │ sum_result │ point_three_equal │ cents_equal │ ├────────────┼───────────────────┼─────────────┤ │ 0.3 │ 0 │ 1 │ └────────────┴───────────────────┴─────────────┘ ``` The sum prints as `0.3` on screen but is not `0.3`. Display rounds; comparison does not. If a late fee is held in this type, the query testing whether the amount paid equals the amount owed sometimes returns the wrong answer, and the error is below a cent — no one looking at the log can see it. There are three options. **Integer** types are exact; for money they are used by choosing the smallest unit — an amount is stored as an integer count of cents and divided by a hundred for display. **Decimal** types (`NUMERIC`, `DECIMAL`) offer exact decimal arithmetic to a fixed number of digits and are the standard choice for a monetary column. **Floating-point** types are for values that are already approximate by nature — measurements, ratios, scientific magnitudes. The rule is: a counted quantity takes an integer or decimal type, a measured quantity takes a floating-point type. Money is counted. A second criterion for an integer type is width. A narrow type saves space but brings the overflow limit closer; choosing a two-byte type for a member number means no more than sixty-five thousand members can ever be recorded. How overflow behaves — wrapping or raising an error — varies by engine; choosing the limit generously from the start is cheaper than changing the type later. ## Enforcement of a Type That a type is declared does not necessarily mean it is honored: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE book (isbn TEXT PRIMARY KEY, publication_year INTEGER NOT NULL); INSERT INTO book VALUES ('978-975-0000-01-1', 'twenty nineteen'); SELECT publication_year, typeof(publication_year) AS stored_type FROM book; SQL ``` ``` ┌──────────────────┬─────────────┐ │ publication_year │ stored_type │ ├──────────────────┼─────────────┤ │ twenty nineteen │ text │ └──────────────────┴─────────────┘ ``` The column is declared as an integer; the value stored is text. **How binding a type declaration is varies by engine**: some engines attempt a conversion and reject it on failure, some accept the value as given. The portable defense is again the constraint — the domain definition bounds the value independent of the type: ```sh sqlite3 :memory: <<'SQL' CREATE TABLE book ( isbn TEXT PRIMARY KEY NOT NULL, publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1450 AND 2100) ); INSERT INTO book VALUES ('978-975-0000-01-1', 'twenty nineteen'); SQL ``` ``` Runtime error near line 5: CHECK constraint failed: publication_year BETWEEN 1450 AND 2100 (19) ``` ## Text and Collation Text types split along two axes. **Length**: a fixed-length type uses the same space in every row and pads short values with spaces; a variable-length type stores only what is written. Fixed length belongs only to genuinely fixed data — a two-letter country code, a three-character branch code. **Limit**: a length limit is a domain constraint; the rule that a branch code is three characters is not expressed by an unbounded text column. The real subtlety is in comparison. Whether two pieces of text are considered equal, and which comes first, depends on **collation**: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box SELECT 'ayse' = 'AYSE' AS binary_comparison, 'ayse' = 'AYSE' COLLATE NOCASE AS collated_comparison, 'ayşe' = 'AYŞE' COLLATE NOCASE AS turkish_lettered; SQL ``` ``` ┌───────────────────┬─────────────────────┬──────────────────┐ │ binary_comparison │ collated_comparison │ turkish_lettered │ ├───────────────────┼─────────────────────┼──────────────────┤ │ 0 │ 1 │ 0 │ └───────────────────┴─────────────────────┴──────────────────┘ ``` The default comparison is byte for byte: `ayse` and `AYSE` differ. Choosing a case-insensitive collation equates the two, but `ayşe` and `AYŞE` still do not equate — this collation covers only the base Latin letters. **Collation names and their coverage vary by engine**; a schema working with Turkish text needs a language-aware collation chosen explicitly. The normalization problem from the Character Encodings lesson resurfaces here: two visually identical strings do not count as equal when their code point sequences differ. ## Date and Time Three separate concepts exist for date and time: date alone, time alone, and the timestamp that joins the two. The timestamp carries one further distinction — is the value tied to a time zone, or is it local wall-clock time? A library's opening hour, "09:00," is a local rule; the instant a loan transaction occurs is a universal point and must be stored together with its time zone. In environments without a dedicated date type, text in ISO 8601 format is used. The valuable property of this format is that lexicographic order equals chronological order: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box .nullvalue (null) CREATE TABLE loan (loan_no INTEGER PRIMARY KEY, pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO loan VALUES (1001, '2025-03-02', '2025-03-16'), (1002, '2025-03-04', '2025-04-01'), (1003, '2025-12-30', NULL); SELECT loan_no, pickup_date, julianday(return_date) - julianday(pickup_date) AS days, pickup_date > '2025-03-03' AS after_march_third FROM loan ORDER BY pickup_date; SQL ``` ``` ┌─────────┬─────────────┬────────┬───────────────────┐ │ loan_no │ pickup_date │ days │ after_march_third │ ├─────────┼─────────────┼────────┼───────────────────┤ │ 1001 │ 2025-03-02 │ 14.0 │ 0 │ │ 1002 │ 2025-03-04 │ 28.0 │ 1 │ │ 1003 │ 2025-12-30 │ (null) │ 1 │ └─────────┴─────────────┴────────┴───────────────────┘ ``` Sorting and range comparison work correctly on the text. Arithmetic, however, requires date functions, and **the names of these functions vary by engine**; a function that converts to a day count was used here to compute the day difference. This is the cost of the text representation: there is no type checking, `'2025-13-45'` can be written, and every operation requires parsing. Where an engine has a dedicated date type, that type is chosen. Using a zeroed-out date or a placeholder like `'0000-00-00'` is a workaround for dodging the null-value discussion from the previous lesson, and it requires that placeholder to be filtered out of every query. A null value is used for an unknown date. ## Truth Values, Binary Data, and Enumeration Three more common cases remain. - **Truth value.** In engines without a dedicated type, a single-digit integer column is used, paired with a `CHECK (column IN (0, 1))` constraint. Without the constraint, the column can take a third value, and there is no way to trace which code wrote it. - **Binary data.** Data such as a cover image or a scanned document can be stored in binary types. The criterion is size: binary data that is small and meaningful together with the row can stay in the column; for large objects, keeping them in a file system or object store and storing only the address in the database lowers backup size and row-read cost. The cost of this is that consistency between the two storage locations now falls to the application. - **Enumeration.** A loan transaction's status — open, overdue, closed — is a bounded value set. There are two counterparts: a `CHECK ... IN (...)` constraint on a text column, or a foreign key to a separate relation that holds the values as rows. The second turns adding a new value from a schema change into a data change, and it allows attaching extra information — a display name, a sort order — to the value. ## Summary - Counted quantities are stored with an integer or decimal type, measured quantities with a floating-point type; money is counted. - A floating-point sum can display correctly on screen while an equality comparison still returns the wrong result. - How binding a type declaration is varies by engine; a domain's real limit, once written as a constraint, holds in every engine. - Text equality and ordering depend on collation; case insensitivity does not extend to Turkish letters on its own. - In the absence of a dedicated date type, ISO 8601 text is used; sorting works correctly, and arithmetic requires an engine-specific function. ## Next Step The Relational Model topic concludes here: the relation was defined, keys were chosen, constraints were written, the logic of null values was established, and columns were given types. One question remains — which relation should a column belong to? In the course's first lesson, the member name repeated across three rows in the `loan.csv` file, and an email change required editing three places. The next topic opens with the concept that explains where that repetition comes from: functional dependency.