zetaloop
22 lines
851 B
SQL
22 lines
851 B
SQL
CREATE TABLE shop_players
|
|
(
|
|
id BIGINT PRIMARY KEY,
|
|
shop_id BIGINT NOT NULL REFERENCES shops (id),
|
|
player_id BIGINT NOT NULL,
|
|
|
|
-- 标记是否为主店铺。用于个人主页展示和 players.shop_id 缓存源
|
|
is_primary BOOLEAN DEFAULT FALSE,
|
|
joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
left_at TIMESTAMPTZ, -- 软删除，表示已离职
|
|
|
|
UNIQUE (shop_id, player_id)
|
|
);
|
|
|
|
-- 索引
|
|
CREATE INDEX idx_shop_players_player ON shop_players (player_id) WHERE left_at IS NULL;
|
|
CREATE INDEX idx_shop_players_shop_active ON shop_players (shop_id, joined_at DESC) WHERE left_at IS NULL;
|
|
|
|
-- 唯一索引：确保一个打手同一时间只能有一个主店铺
|
|
CREATE UNIQUE INDEX idx_shop_players_one_primary
|
|
ON shop_players (player_id) WHERE is_primary = TRUE AND left_at IS NULL;
|