database-standard

Database Preferences

• Forbidden: MySQL
• Recommended: PostgreSQL or other modern databases
• Required: Foreign key constraints for data integrity

SQL Code Style

• All lowercase
• SQL files have no comments

Primary Key Standard

| Field | Type | Description | |-------|------|-------------| | id | bigint | Primary key, required for all regular tables |

Audit Fields

All tables except junction tables MUST include:

| Field | Full Name | Type | Default | Description | |-------|-----------|------|---------|-------------| | crd | create row datetime | timestamp | current_timestamp | Row creation time, timezone-independent | | mrd | modify row datetime | timestamp | null | Last modification time, timezone-independent, nullable | | rlv | row lock version | integer | 0 | Optimistic lock version |

Soft Delete Field

| Field | Full Name | Type | Default | Description | |-------|-----------|------|---------|-------------| | ldf | logic delete field | timestamp | null | Soft delete time, timezone-independent, null means active |

Junction Table Standard

Tables linking two entities:

• No primary key
• No audit fields
• Only foreign key IDs from both tables

Tree Structure

Tables with upward lookup (e.g., address) use pid for parent link:

| Field | Type | Description | |-------|------|-------------| | pid | bigint | Parent primary key, nullable |

Examples

Regular Table

create table user (
    id bigint primary key,
    name varchar(255) not null,
    email varchar(255),
    ldf timestamp,
    crd timestamp not null default current_timestamp,
    mrd timestamp,
    rlv integer not null default 0
);

Junction Table

create table user_role (
    user_id bigint not null references user(id),
    role_id bigint not null references role(id)
);

Tree Table

create table address (
    id bigint primary key,
    pid bigint references address(id),
    name varchar(255) not null,
    crd timestamp not null default current_timestamp,
    mrd timestamp,
    rlv integer not null default 0
);