Database design standards defining primary keys, foreign keys, audit fields, soft delete, junction tables. PostgreSQL style preferred, SQL lowercase without comments.
| Field | Type | Description |
|---|---|---|
id | bigint | Primary key, required for all regular tables |
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 |
| Field | Full Name | Type | Default | Description |
|---|---|---|---|---|
ldf | logic delete field | timestamp | null | Soft delete time, timezone-independent, null means active |
Tables linking two entities:
Tables with upward lookup (e.g., address) use pid for parent link:
| Field | Type | Description |
|---|---|---|
pid | bigint | Parent primary key, nullable |
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
);
create table user_role (
user_id bigint not null references user(id),
role_id bigint not null references role(id)
);
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
);