团队每日配对轮值应用SQL表结构优化咨询——替代数组存储外键的方案建议

Hey there! Let's break down how to refactor your database design to ditch those array columns and adopt a fully normalized relational structure—this will make your data more consistent, queries easier to write, and maintenance a breeze.

First, let's talk about why the original array-based approach can cause headaches:

• No referential integrity: Arrays don’t support foreign key constraints, so you could end up storing invalid member IDs that don’t exist in the member table.
• Hard to query/modify: To get specific members in a tab or adjust pairings, you’d have to unpack arrays every time—slow and error-prone.
• Poor scalability: Adding new members to a tab or adjusting pair groups requires editing array values instead of simple insert/delete operations.

Adjusted Table Structure #

Here’s a normalized design that fixes these issues while meeting all your requirements:

1. Core Tables (Unchanged with Minor Improvements)

These keep track of teams and their members, with better constraints to ensure data consistency:

create table if not exists team (
 id serial not null primary key,
 name text not null unique -- Prevent duplicate team names
);

create table if not exists member (
 id serial not null primary key,
 team_id integer not null references team(id) on delete cascade, -- Members are deleted if their team is removed
 nickname text not null
);

2. Team Tab & Member Association

Instead of storing member_ids as an array, use a junction table to link tabs to their members. This enforces valid member-tab relationships and makes it easy to add/remove members:

create table if not exists team_tab (
 id bigserial not null primary key,
 team_id integer not null references team(id) on delete cascade,
 name text not null,
 unique(team_id, name) -- Ensure unique tab names per team
);

-- Junction table for tab-member relationships
create table if not exists team_tab_member (
 id bigserial not null primary key,
 team_tab_id integer not null references team_tab(id) on delete cascade,
 member_id integer not null references member(id) on delete restrict, -- Block member deletion if they're still in a tab
 unique(team_tab_id, member_id) -- Prevent duplicate members in the same tab
);

3. Daily Pairing Groups

For daily pairings, split the data into two tables: one for pairing groups (per tab per day) and another for linking members to those groups. This supports single or multiple pair groups per day:

-- Tracks each pairing group for a tab on a specific date
create table if not exists team_tab_daily_pair_group (
 id bigserial not null primary key,
 team_tab_id integer not null references team_tab(id) on delete cascade,
 tab_date date not null,
 group_order integer not null, -- Distinguishes multiple groups on the same day (e.g., Group 1, Group 2)
 unique(team_tab_id, tab_date, group_order) -- No duplicate groups for the same tab/date/order
);

-- Links members to their pairing group
create table if not exists pair_group_member (
 id bigserial not null primary key,
 pair_group_id integer not null references team_tab_daily_pair_group(id) on delete cascade,
 member_id integer not null references member(id) on delete restrict,
 unique(pair_group_id, member_id) -- No duplicate members in the same pairing group
);

Key Benefits of This Design #

• Referential integrity: Foreign keys ensure all linked members/tabs exist—no invalid IDs floating around.
• Flexible queries: Easily fetch members in a tab, pairings for a date, or a member’s pairing history with simple joins.
• Easy maintenance: Add/remove tab members or adjust pair groups with basic INSERT/DELETE statements, no array editing required.
• Scalability: Add fields like pairing notes, group types, or rotation rules later without restructuring core tables.

Example Queries #

To show how this works in practice, here are a few common queries:

Get All Members in a Specific Tab

select m.nickname
from member m
join team_tab_member ttm on m.id = ttm.member_id
join team_tab tt on ttm.team_tab_id = tt.id
where tt.name = 'Backend Support' and tt.team_id = 1; -- Replace with your team/tab details

Fetch All Pair Groups for a Tab on a Specific Date

select ttdpg.group_order, array_agg(m.nickname) as pair_members
from team_tab_daily_pair_group ttdpg
join pair_group_member pgm on ttdpg.id = pgm.pair_group_id
join member m on pgm.member_id = m.id
join team_tab tt on ttdpg.team_tab_id = tt.id
where tt.name = 'Frontend Rotation' and ttdpg.tab_date = '2024-05-20'
group by ttdpg.group_order
order by ttdpg.group_order;

This design aligns with relational database best practices and will serve your team pairing rotation app well as it grows.

内容的提问来源于stack exchange，提问作者Legionas