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

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