代码之家 › 专栏 › 技术社区 › Noah Goodrich

数据库设计问题与关系

relational-database foreign-keys database-design mysql php

Noah Goodrich · 技术社区 · 16 年前

我正在为一个现有的数据库进行升级,这个数据库是在没有任何代码的情况下设计的,以实现正在考虑的设计。现在,在用代码实现数据库设计方面,我遇到了困难。我确定这是否是数据库设计的问题,或者我只是没有找到正确的解决方案来解决需要做的事情。

基本逻辑规定如下:

用户通过座位访问在线培训。用户可以有多个座位。
座椅由公司购买,与产品有多对多关系。
产品与模块之间存在多对多关系。
一个模块与课程有多对多的关系。
课程是最终用户获得培训的途径。
为了混水,出于某种原因,一些用户拥有多个包含相同产品的座位。
认证是以每个产品为基础,而不是以每个座位为基础。
用户与课程有多对多关系,这些课程存储了他们当前的状态或课程分数。
当用户完成产品所有模块中的所有课程时,他们会为产品认证。
了解用户何时完成特定模块的所有课程也很重要。
一些席位将用于重新认证,这意味着以前为产品认证的用户可以注册并参加重新认证考试。
根据规则11,用户可以并将拥有多个认证记录。
编辑:当用户完成一个课程(分数超过80%)时,用户已经(根据当前业务逻辑)完成了所有产品和包含该课程的所有座位的课程。

我一直遇到的问题是,我或多或少地描述了当前的设计和业务逻辑,我找不到一种方法来有效地联系用户是否已经为特定的产品和座椅认证,如果他们没有。我不断遇到障碍,试图确定哪些产品下的座位已经为用户认证,哪些没有。部分问题是,如果他们目前在不同的座位下注册了同一产品的多个,那么我只需要计算一次产品。

下面是所涉及的架构部分的副本。对于如何改进设计或在代码中绘制关联的建议,我们将不胜感激。如果重要的话,这个地方是建在灯堆上的。

您可以在此处查看数据库架构的相关部分: http://lpsoftware.com/problem_db_structure.png

4 回复 | 直到 12 年前

finnw 16 年前

你要找的是 relational division 不直接在SQL中实现,但可以实现。在谷歌搜索其他例子。

Gaurav 16 年前

在快速查看模式之后,我认为您可以做的一件事是创建一个“to-be-u-certified”表。当产品被分配到座位时(当产品被填充时),用用户ID、产品ID和座位ID填充它。

在将记录添加到“认证”表中时,删除“待认证”表中的相应记录。这将使您能够方便地访问为用户认证的所有产品和未认证的产品。

要消除重复的产品ID,可以按产品ID分组。

Bill Karwin 16 年前

您需要更改lessonstatus rtab表:

CREATE TABLE lessonstatus_rtab (
  user_id    INT NOT NULL,
  seat_id    INT NOT NULL,
  lesson_id  INT NOT NULL REFERENCES lesson_rtab,
  accessdate TIMESTAMP,
  score      NUMERIC(5,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, seat_id, lesson_id),
  FOREIGN KEY (user_id, seat_id) REFERENCES user_seat_rtab (user_id, seat_id)
);

然后,您可以查询用户拥有座位的每个产品,他是否经过认证?这假定他所获得的课程数,比如说,50%或更高的课程数与产品所有模块中的课程数相同。

SELECT p.name, us.user_id, us.seat_id, COUNT(l.id) = COUNT(lu.lesson_id) AS is_certified
FROM user_seat_rtab AS us
 JOIN seat_rtab AS s ON (s.id = us.seat_id)
 JOIN product_seat_rtab AS ps ON (ps.seat_id = s.id)
 JOIN product_rtab AS p ON (p.id = ps.product_id)
 JOIN product_module_rtab AS pm ON (pm.product_id = p.id)
 JOIN module_rtab AS m ON (m.id = pm.module_id)
 JOIN module_lesson_rtab AS ml ON (ml.module_id = m.id)
 JOIN lesson_rtab AS l ON (l.id = ml.lesson_id)
 LEFT OUTER JOIN lessonstatus_rtab AS lu 
   ON (lu.lesson_id = l.id AND lu.user_id = us.user_id 
     AND lu.seat_id = us.seat_id AND lu.score > 0.50)
GROUP BY p.id, us.user_id, us.seat_id;

Noah Goodrich 16 年前

更新:

我进一步考虑了这个问题,并考虑了是否允许更好的工作,简单地删除用户座位表,然后使用等效的认证座位表(可能重命名)来保存有关用户座位状态的所有信息。这样,用户、他们的座位、座位内的每个产品以及用户是否已为特定产品和座位认证之间就建立了直接关系。

因此,我会将以下更改应用于随问题发布的模式:

DROP TABLE user_seat_rtab;
RENAME TABLE certification_rtab TO something_different;

进一步规范这种新结构的另一种方法是这样做:

ALTER TABLE user_seat_rtab 
    DROP PRIMARY KEY;
    ADD COLUMN product_id int(10) unsigned NOT NULL;
    ADD CONSTRAINT pk_user_seat_product PRIMARY KEY (user_id, seat_id, product_id);
    ADD CONSTRAINT fk_product_user_seat FOREIGN KEY (product_id) REFERENCES product_rtab(id) ON DELETE RESTRICT;

我不太确定这是否能解决问题,或者在引入新问题的同时它是否会稍微改变问题的性质。那么,有人有其他的批评或建议吗?