5.2. Database Design Rules

5.2.1. Table Naming

Rule	Example
Lowercase + snake_case	user_profiles
Use plural form	users, orders
No prefixes	tbl_users (X) → users (O)
No reserved words	user (X) → users (O)

5.2.2. Column Naming

Rule	Example
Lowercase + snake_case	created_at, user_name
No table name prefix	user_user_name (X) → user_name (O)
Boolean columns	is_ or has_ prefix
Date/time columns	_at suffix

5.2.3. Required Columns

All tables must include the following columns.

Column	Type	Description
id	BIGSERIAL or UUID	Primary key
created_at	TIMESTAMP WITH TIME ZONE	Creation timestamp
updated_at	TIMESTAMP WITH TIME ZONE	Modification timestamp

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    updated_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

5.2.4. Primary Key Strategy

Strategy	Type	Usage Criteria
BIGSERIAL	Auto-incrementing integer	General business entities (default)
UUID	uuid	Distributed environments, externally exposed identifiers

When using UUID:

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE documents (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title       VARCHAR(255) NOT NULL,
    created_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    updated_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

5.2.5. Index Naming

idx_{table_name}_{column_name}

Type	Naming Convention	Example
Regular index	idx_{table}_{column}	idx_users_email
Composite index	idx_{table}_{col1}_{col2}	idx_orders_user_id_status
Unique index	uniq_{table}_{column}	uniq_users_email

CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX uniq_users_email ON users (email);
CREATE INDEX idx_orders_user_id_status ON orders (user_id, status);

5.2.6. Foreign Key Naming

fk_{table_name}_{referenced_table_name}

ALTER TABLE orders
    ADD CONSTRAINT fk_orders_users
    FOREIGN KEY (user_id) REFERENCES users (id);

5.2.7. Prohibited Practices

Prohibited Item	Reason
Composite primary keys	Increases join complexity, complicates jOOQ code generation
Overuse of TEXT type	Columns without length limits should only be used when explicitly necessary
FLOAT / DOUBLE	Precision issues with financial data → use NUMERIC
Reserved word table/column names	SQL compatibility issues
Trigger-based business logic	Must be handled in the application layer