SQL中为不同研究创建专属表的替代方案咨询（医院样本库存系统）

针对医院生物样本库存系统的动态字段需求，结合SQLite的特性，推荐以下三种成熟方案，无需为每个研究创建独立表：

1. 实体-属性-值（EAV）模型：强规范的动态字段 #

这是动态属性存储的经典方案，通过拆分「研究字段定义」和「样本属性值」来实现差异化配置，适合对数据类型和字段规范要求严格的医疗场景。

核心表结构 #

-- 研究基础信息表
CREATE TABLE studies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 用户-研究多对多关联表
CREATE TABLE user_studies (
    user_id INTEGER NOT NULL,
    study_id INTEGER NOT NULL,
    PRIMARY KEY (user_id, study_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (study_id) REFERENCES studies(id)
);

-- 研究自定义字段定义表（约束每个研究的字段规则）
CREATE TABLE study_attributes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    study_id INTEGER NOT NULL,
    attribute_name TEXT NOT NULL,
    data_type TEXT NOT NULL CHECK(data_type IN ('INT', 'TEXT', 'REAL', 'DATE')),
    is_required BOOLEAN DEFAULT 0,
    FOREIGN KEY (study_id) REFERENCES studies(id),
    UNIQUE(study_id, attribute_name) -- 同一研究下字段名唯一
);

-- 样本核心信息表（存储所有研究通用的字段）
CREATE TABLE samples (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    study_id INTEGER NOT NULL,
    sample_barcode TEXT NOT NULL UNIQUE,
    created_by INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES studies(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- 样本属性值存储表（每个样本的自定义字段值）
CREATE TABLE sample_attribute_values (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sample_id INTEGER NOT NULL,
    attribute_id INTEGER NOT NULL,
    value_text TEXT,
    value_int INTEGER,
    value_real REAL,
    value_date DATETIME,
    FOREIGN KEY (sample_id) REFERENCES samples(id),
    FOREIGN KEY (attribute_id) REFERENCES study_attributes(id),
    UNIQUE(sample_id, attribute_id) -- 同一样本同一属性只能存一个值
);

优缺点 #

• 优势：数据类型约束严格，可通过数据库层面保证字段规范，契合医疗场景的数据严谨性要求。
• 劣势：查询逻辑复杂，需要多表关联；数据量大时，关联查询的性能会有所下降（SQLite中小规模数据可忽略）。

2. JSON字段存储：轻量高效的动态方案 #

利用SQLite 3.31.0+原生支持JSON的特性，将每个样本的自定义字段打包存储为JSON对象，开发效率高，查询逻辑简单。

核心表结构 #

-- 研究表、用户-研究关联表同上，此处省略
-- 研究字段规范表（用于应用层校验，避免随意添加字段）
CREATE TABLE study_attribute_definitions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    study_id INTEGER NOT NULL,
    attribute_name TEXT NOT NULL,
    data_type TEXT NOT NULL CHECK(data_type IN ('INT', 'TEXT', 'REAL', 'DATE')),
    is_required BOOLEAN DEFAULT 0,
    FOREIGN KEY (study_id) REFERENCES studies(id),
    UNIQUE(study_id, attribute_name)
);

-- 样本表（新增JSON字段存储自定义属性）
CREATE TABLE samples (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    study_id INTEGER NOT NULL,
    sample_barcode TEXT NOT NULL UNIQUE,
    created_by INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    custom_attributes JSON DEFAULT '{}', -- 自定义字段以JSON格式存储
    FOREIGN KEY (study_id) REFERENCES studies(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- 针对常用自定义字段创建索引（优化查询性能）
CREATE INDEX idx_sample_collection_temp ON samples (json_extract(custom_attributes, '$.collection_temperature'));

示例操作 #

• 插入样本（带自定义字段）：

INSERT INTO samples (study_id, sample_barcode, created_by, custom_attributes)
VALUES (1, 'BARCODE_001', 123, '{"collection_temperature": 25.5, "sample_source": "外周血", "collection_date": "2024-05-20"}');

• 查询样本（包含自定义字段）：

SELECT
    sample_barcode,
    created_at,
    json_extract(custom_attributes, '$.collection_temperature') AS temp,
    json_extract(custom_attributes, '$.sample_source') AS source
FROM samples
WHERE study_id = 1;

优缺点 #

• 优势：开发、查询逻辑简单，无需复杂关联；SQLite对JSON的支持足够应对中小规模医疗样本数据。
• 劣势：数据库层面的类型约束较弱，需要在应用层通过study_attribute_definitions做校验；高频查询的自定义字段需单独建索引。

3. 混合模型：兼顾规范与效率 #

如果系统既有大量通用字段，又有差异化的自定义需求，可以结合上述两种方案：将通用字段（如样本条形码、所属研究、创建人）放在普通列，自定义字段用JSON存储，同时用study_attribute_definitions表约束字段规则。这种方案平衡了数据规范和开发效率，是医疗场景中最常用的折中方案。

实践注意事项 #

1. 应用层校验：无论采用哪种方案，都必须在应用层根据研究的字段定义，校验用户输入的字段类型、必填项，避免脏数据进入数据库。
2. 性能优化：SQLite适合中小规模数据（百万级以内），如果未来数据量增长，可考虑对高频查询字段建索引，或定期归档历史数据。
3. 权限控制：结合user_studies多对多表，在应用层控制用户只能访问自己参与的研究的样本数据。

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