sqlite 在SQL中将多个表分配给用户的替代方法是什么？

cl25kdpy 于 2023-03-03 发布在 SQLite

关注(0)|答案(1)|浏览(147)

我正在为一家医院制作一个包含生物样本数据的库存系统。我正在使用SQLite作为数据库。这个系统应该可以理想地用于各种研究。
由于每个研究和每个用户都将有不同的字段，以及他们各自研究所需的不同数量的字段，我想使这些字段用户可配置，而不是静态的。我想到的一种添加此功能的方法是允许用户创建一个“研究”，该研究将在内部创建一个专门用于该研究的SQL表。用户可能有多个研究，并且因此可能具有与其账户相关联的多个完整的“表”。
我该如何着手完成这件事呢？我在下面附上了截图，最终我想向用户展示的是什么。
假设用户参与了主页上显示的3项不同研究，点击研究1后会看到：

同样，对于研究2和3：

多个用户可能是多个研究的一部分。我知道我可以用一个中间的多对多关系来完成这一点。
我的主要问题是，如何为不同的案例设置不同数量的字段？目前我能想到的唯一方法是在每次用户创建新案例时创建一个SQL表。不过，我知道这不是最佳实践。谢谢。

sqlite

来源：https://stackoverflow.com/questions/75561849/alternative-to-assigning-multiple-tables-to-users-in-sql

1条答案

按热度按时间

xurqigkl1#

可以吗我觉得这是个禁忌。
禁忌没有，但与规范化和良好使用关系数据库的关系方面相反。
当你有一个属于其他东西的东西的变化列表时，那么两个具有合适关系的表就可以处理。

- 简而言之，正如许多人所说，有一个更好的办法：**

您提到一项研究具有不同数量的字段，因此有2个表study和field，字段可以与研究具有多对1的关系。因此，研究可以具有0-多个字段（显然，空研究没有什么用处）。
因此，开始时您可以：-

CREATE TABLE IF NOT EXISTS study (
    studyid INTEGER PRIMARY KEY, 
    studyname TEXT, 
    studystartdate TEXT, 
    studyenddate TEXT
);
CREATE TABLE IF NOT EXISTS field (
    fieldid INTEGER PRIMARY KEY, 
    owningstudy INTEGER REFERENCES study(studyid) ON DELETE CASCADE ON UPDATE CASCADE,
    fieldtext TEXT
);

与所属研究相关的owningstudy。一个字段只能与一项研究相关，但一项研究可以有多个字段与其相关。

您提到了用户，因此需要一个用户表，例如

CREATE TABLE IF NOT EXISTS user (
    userid INTEGER PRIMARY KEY,
    username TEXT,
    userdob TEXT
);

你提到了与一个字段相关的样本，也提到了与一个用户相关的样本，并且每个字段（属于一个研究）每个用户有多少样本是可变的。
多对多关系通常（仅？）由中间表（具有各种名称，如关联表、Map表、引用表...）提供。这样的表至少有两列，一列与其中一个表相关，另一列与另一个表相关。主键通常是这两列的组合。简而言之，您可以具有：-

CREATE TABLE IF NOT EXISTS sample (
    fieldidmap INTEGER REFERENCES field(fieldid) ON DELETE CASCADE ON UPDATE CASCADE,
    useridmap INTEGER REFERENCES user(userid) ON DELETE CASCADE ON UPDATE CASCADE,
    reading REAL,
    PRIMARY KEY (useridmap,fieldidmap)
);

注意，REFERENCES用于添加一个约束（规则），以防止无效值（孤立值）。不强制要求具有这样的约束（但建议使用）。
CASCADES将父表（被引用表）上的动作级联到子表（所有者）。
这通过还包括用于与样本相关联的读数的列来扩展纯Map/关系。

因此，使用上面你可以添加一些数据，如：-

INSERT INTO study VALUES 
    (100,'A','2023-01-01','2023-01-31')
    ,(101,'B','2023-02-01','2023-02-28')
    ,(102,'C','2023-03-01','2023-03-31')
;
INSERT INTO field VALUES
    (500,100,'field1')
    ,(501,100,'field2')
    ,(502,100,'field3')
    ,(503,101,'field1')
    ,(504,102,'field1')
    ,(505,102,'field2')
    ,(506,102,'field3')
    ,(507,102,'field4')
;
INSERT INTO user VALUES
    (800,'Fred','2000-01-01'),
    (801,'Jane','1998-09-18'),
    (802,'Mary','1999-10-11'),
    (803,'Sue','2001-11-10'),
    (804,'Tom','1996-13-03')
;
INSERT INTO sample VALUES
    (500,800,11.11),(500,803,22.22)
    ,(501,803,33.33)
    ,(502,800,44.44),(502,804,55.55),(502,802,66.66)
    ,(503,804,77.77),(503,803,88.88)
    ,(505,802,99.99),(505,801,12.12),(505,804,13.13)
    ,(506,800,14.14),(506,801,15.15),(506,802,16.16),(506,803,17.17),(506,804,18.18)
    ,(507,803,19.19)
;

取第一个值（500，800，11.11）。这

Map到字段表中字段id值为500的行（字段1的第一个），这隐含地与所有检查相关，即名称为A的检查，以及
也发送给具有用户800的用户，以及
读数为11.11

等等。
现在考虑以下SELECT查询作为示例：

SELECT study.*, field.fieldtext,user.username,user.userdob,sample.reading
FROM study
    JOIN field ON field.owningstudy = study.studyid
    JOIN sample ON sample.fieldidmap = field.fieldid
    JOIN user ON user.userid = sample.useridmap
;

结果是（加上上述数据）：

100 A   2023-01-01  2023-01-31  field1  Fred    2000-01-01  11.11
100 A   2023-01-01  2023-01-31  field1  Sue 2001-11-10  22.22
100 A   2023-01-01  2023-01-31  field2  Sue 2001-11-10  33.33
100 A   2023-01-01  2023-01-31  field3  Fred    2000-01-01  44.44
100 A   2023-01-01  2023-01-31  field3  Tom 1996-13-03  55.55
100 A   2023-01-01  2023-01-31  field3  Mary    1999-10-11  66.66
101 B   2023-02-01  2023-02-28  field1  Tom 1996-13-03  77.77
101 B   2023-02-01  2023-02-28  field1  Sue 2001-11-10  88.88
102 C   2023-03-01  2023-03-31  field2  Mary    1999-10-11  99.99
102 C   2023-03-01  2023-03-31  field2  Jane    1998-09-18  12.12
102 C   2023-03-01  2023-03-31  field2  Tom 1996-13-03  13.13
102 C   2023-03-01  2023-03-31  field3  Fred    2000-01-01  14.14
102 C   2023-03-01  2023-03-31  field3  Jane    1998-09-18  15.15
102 C   2023-03-01  2023-03-31  field3  Mary    1999-10-11  16.16
102 C   2023-03-01  2023-03-31  field3  Sue 2001-11-10  17.17
102 C   2023-03-01  2023-03-31  field3  Tom 1996-13-03  18.18
102 C   2023-03-01  2023-03-31  field4  Sue 2001-11-10  19.19

研究A有6个样本2个用于Fred（字段1和字段3），2个用于Sue（字段1和字段2），1个用于Tom（字段3），1个用于Mary（字段3）。
研究B有2份样品......
研究C有9份样品

从用户视点（即使用ORDER BY user.userid, study.studyid按用户ID排序），则完全相同的数据将生成：-

100 A   2023-01-01  2023-01-31  field1  Fred    2000-01-01  11.11
100 A   2023-01-01  2023-01-31  field3  Fred    2000-01-01  44.44
102 C   2023-03-01  2023-03-31  field3  Fred    2000-01-01  14.14
102 C   2023-03-01  2023-03-31  field2  Jane    1998-09-18  12.12
102 C   2023-03-01  2023-03-31  field3  Jane    1998-09-18  15.15
100 A   2023-01-01  2023-01-31  field3  Mary    1999-10-11  66.66
102 C   2023-03-01  2023-03-31  field2  Mary    1999-10-11  99.99
102 C   2023-03-01  2023-03-31  field3  Mary    1999-10-11  16.16
100 A   2023-01-01  2023-01-31  field1  Sue 2001-11-10  22.22
100 A   2023-01-01  2023-01-31  field2  Sue 2001-11-10  33.33
101 B   2023-02-01  2023-02-28  field1  Sue 2001-11-10  88.88
102 C   2023-03-01  2023-03-31  field3  Sue 2001-11-10  17.17
102 C   2023-03-01  2023-03-31  field4  Sue 2001-11-10  19.19
100 A   2023-01-01  2023-01-31  field3  Tom 1996-13-03  55.55
101 B   2023-02-01  2023-02-28  field1  Tom 1996-13-03  77.77
102 C   2023-03-01  2023-03-31  field2  Tom 1996-13-03  13.13
102 C   2023-03-01  2023-03-31  field3  Tom 1996-13-03  18.18

例如，Fred提交了3份样本，其中2份用于研究A，1份用于研究C（显然，研究B为0）

赞(0）回复(0）举报 2023-03-03

我来回答

sqlite 在SQL中将多个表分配给用户的替代方法是什么？

1条答案

相关问题

热门标签

最新问答