database-standard

TrueNine 的头像作者 TrueNine

定义主键、外键、审计字段、软删除和连接表的数据库设计标准。首选 PostgreSQL 风格,SQL 小写且不带注释。

📖 中文导读:定义主键、外键、审计字段、软删除和连接表的数据库设计标准。首选 PostgreSQL 风格,SQL 小写且不带注释。 以下正文为该技能的原始 SKILL.md 内容(英文),上方安装命令可直接使用。

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