Types, Keys and Conventions

Choose data types, keys and naming conventions that keep a schema clear and correct over time.

In this lesson you will

Choose appropriate data types for each column
Choose keys and apply naming conventions consistently

Key concepts

data types (INTEGER, NUMERIC, TEXT, DATE, BOOLEAN)surrogate vs natural keysnaming conventionsdocumenting a schema

The lesson

Pick types that match the data and enforce it: DATE for dates rather than text, NUMERIC for money in PostgreSQL (or whole units such as SSP in INTEGER), BOOLEAN for yes/no. The right type rejects bad values and makes sorting and calculations correct.

Use surrogate keys (generated IDs) for most tables, and add UNIQUE constraints on natural identifiers such as a registration number, so duplicates are still prevented.

Use one naming convention throughout — for example lower-case snake_case, singular or plural tables consistently, foreign keys named after the table (household_id). Document each table and column briefly.

Step by step

Assign data types to every column of the NGO design.
Add surrogate keys and UNIQUE constraints.
Rename columns to the agreed convention.
Write the schema script and a short data dictionary.

Practice activity

Write the NGO schema script with types, keys, constraints and a data dictionary.

Expected result: A schema that runs cleanly, rejects invalid data and is documented column by column.

Check your understanding

Why store dates in a DATE column rather than text?

Show answer
Invalid dates are rejected and sorting and date calculations work correctly.
Why add a UNIQUE constraint when using a surrogate key?

Show answer
To prevent duplicates of the real-world identifier.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.