database-standard

---

name: database-standard description: Database design standards defining primary keys, foreign keys, audit fields, soft delete, junction tables. PostgreSQL style preferred, SQL lowercase without comments.

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

Audit Fields

All tables except junction tables MUST include:

Soft Delete Field

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:

Examples

Regular Table

Copycreate 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

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

Tree Table

Copycreate 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
);