Mastering Data Elements: Naming, Domains, and Constraints for Robust SQL Design

1. Data Elements

Before you start designing tables you must consider the data itself: its type, valid range, uniqueness, and a clear, unambiguous name. Joe Celko emphasizes that the first step in relational design is defining the data, not the schema.

The Codd Information Principle states that all data in a relational DBMS is modeled as scalar values in rows and columns, so each column must contain values of the same data element.

2. Naming Conventions

Use ISO‑11179‑style names so that each data element has a single, globally unique identifier. The pattern is: [<role>_]<attribute>_<property> Examples: vehicle_id – a precise identifier for a vehicle (VIN is the ideal standard). sex_code – a coded representation of biological sex (ISO‑5218).

Avoid vague names such as plain id because they convey no meaning about the entity they identify.

3. Suffix Library

_id

– unique identifier (not the table name prefix). _date or _dt – date or datetime dimension. _nbr or _num – numeric label. _name or _nm – readable name. _code or _cd – external standardized code. _size – size or dimension. _seq – sequence number. _tot – total/aggregate. _status – internal status code. _addr or _loc – address or location. _img – image data type.

4. Domains and Data Types

SQL provides three broad categories of data types: Numeric, String, and Temporal. Choose the most appropriate type to avoid errors and to enable meaningful constraints.

Numeric: INTEGER, SMALLINT, DECIMAL, NUMERIC, BIGINT (exact) and REAL, FLOAT, DOUBLE PRECISION (approximate).

String: Fixed‑length CHAR(n), NCHAR(n); variable‑length VARCHAR(n), NVARCHAR(n).

Temporal: DATE, TIME, TIMESTAMP, interval types.

All types allow NULL and have core functions for rounding, casting, and validation.

5. Constraints

Constraints enforce data integrity and help the optimizer produce better execution plans. The most common are NOT NULL, CHECK, and DEFAULT.

Column‑level example:

sale_start_date DATE NOT NULL,

sale_end_date DATE, -- sale is still in progress

Named CHECK constraint (illustrated in the original images):

Complex CASE expression can be used for custom validation, e.g., verifying that a column contains only digits.

Table‑level CHECK example:

6. Default Values

The DEFAULT clause supplies a value when none is provided during INSERT. Typical defaults include CURRENT_TIMESTAMP for dates or sentinel codes for enumerations.

Using defaults reduces boiler‑plate code and ensures consistent data across applications.