请教表设计的问题?

Question

表tbJbxx:nsid,xm,school      主键nsid 
    tbKmOne:nsid,kscs,ksdc  主键nsid+kscs 
    tbKmTwo:nsid,kscs,ksdc  主键nsid+kscs 
    tbKmThree:nsid,ksdc      主键nsid+kscs 
    ksdc为考试等次,只有合格、不合格两种取值,kscs为考试次数 
    一个nsid在tbkmone、tbkmtwo、tbkmthree三个表中可能有多条记录，也可能没有记录，但每个表中只有一条ksdc为合格的记录 
    tbkmone、tbkmtwo、tbkmthree分别与主表tbjbxx通过nsid外键相连； 
    业务逻辑要求是：一个nsid只有在bkmone合格后才能考tbkmtwo、tbkmtwo考试合格后才能考thkmthree,每个nsid对每门科目均可以进行多次考试直至合格 
    设计出现的问题： 
    通过delphi实现客户端业务处理，用户可能在thkmthree中有合格记录后，在tbkmtwo中删除合格记录，至使数据库记载的信息不符合业务逻辑要求。 
    提问：当然可以通过delphi编写客户代码限制上述不合理要求。现在想从源头数据库的表设计上入手，禁止这种删除记录的方法。也就是 tbkmone、tbkmtwo、tbkmthree三个表怎样建立约束关系？

Answer 1

通过delphi实现客户端业务处理，用户可能在thkmthree中有合格记录后，在tbkmtwo中删除合格记录，至使数据库记载的信息不符合业务逻辑要求。这句话的意思是不是说用户在thkmthree中有合格记录后，就需要在tbkmtwo中删除相应的合格记录？即用户通过了thkmthree，肯定也通过了tbkmtwo？另外，致使数据库记载的信息不符合业务逻辑要求，这个要求是什么？能否举个例子说明一下为什么数据库不符合业务逻辑要求呢？这样说明清楚之后，会有高手过来帮助你的

Answer 2

1, 在tbKmOne上建立before delete触发器，如果发现当前nsid记录的ksdc为“合格”，则到tbkmtwo和tbkmthree上去查询，看同一个nsid是否存在ksdc=“合格”的内容，如果存在，不允许删除；2，在tbkmtwo上建立before delete触发器，如果发现nsid记录的ksdc为“合格”，则到tbkmthree上查看是否有nsid为同样值，但ksdc为“合格”的内容，如果存在，则不允许删除。

Answer 3

比如说,某学员thkmthree有一条合格信息,则断定tbkmtwo中肯定有一条合格记录。
现在客户把tbkmtwo中合格的信息删除了，该表中只剩下不合格的记录，而thkmthree中仍然有一条合格记录。
按照业务逻辑要求tbkmtwo中没有合格记录，thkmthree是不应该有记录的，当然更不能有合格记录了。

Answer 4

又是47522341出手了，谢谢您的关注，先试试。
唉，数据库功底不扎实啊，临时抱佛脚，结果搞出这么多问题。
另外昨天您教我的SQL语句还没完全看懂
select M.NSID, m.xm, m.xb, M.SCHOOL, 
  case when a.g1>0 then '合格' 
      when a.g2>0 then '不合格' 
      else null 
  end  ksdcA,  --a科目成绩 
  case when b.g1>0 then '合格' 
      when b.g2>0 then '不合格' 
      else null 
  end  ksdcb,  --b科目成绩 
  case when c.g1>0 then '合格' 
      when c.g2>0 then '不合格' 
      else null 
  end  ksdcc  --c科目成绩 
from m left join 
(select nsid, 
    count(case when ksdc = 'Y' then 1 else null end) g1,  --合格次数 
    count(case when ksdc = 'N' then 1 else null end) g2  --不合格次数 
  from a 
  group by nsid 
) a on m.nsid = a.nsid 
left join 
(select nsid, 
    count(case when ksdc = 'Y' then 1 else null end) g1,  --合格次数 
    count(case when ksdc = 'N' then 1 else null end) g2  --不合格次数 
  from b 
  group by nsid 
) b on m.nsid = b.nsid 
left join 
(select nsid, 
    count(case when ksdc = 'Y' then 1 else null end) g1,  --合格次数 
    count(case when ksdc = 'N' then 1 else null end) g2  --不合格次数 
  from c 
  group by nsid 
) c on m.nsid = c.nsid 
为何可用a.g1、a.g2、a.g3？上行中为何可用c on m.nsid = c.nsid，此处的C是指什么呢？ 

Answer 5

我觉得还可以更简单一点:在tbKmOne的before delete触发器中,如果查询tbkmtwo有记录,则不可以删除。
可能同时还要建updata触发器，因为用户可能不删除合格记录，但可将合格记录更改为不合格，这样同样不合业务逻辑。

Answer 6

 我这里起的别名跟子查询中的源表名称相同，可能引起你的误解了，实际上这里的a.g1指的是子查询的a，第二个问题中的c也是指的子查询后的结果。
    或者，你可以采用下面的做法就会好理解些。
select M.NSID, m.xm, m.xb, M.SCHOOL,
  case when x.g1>0 then '合格'
      when x.g2>0 then '不合格'
      else null
  end  ksdcA,  --a科目成绩
  case when y.g1>0 then '合格'
      when y.g2>0 then '不合格'
      else null
  end  ksdcb,  --b科目成绩
  case when z.g1>0 then '合格'
      when z.g2>0 then '不合格'
      else null
  end  ksdcc  --c科目成绩
from m left join
(select nsid,
    count(case when ksdc = 'Y' then 1 else null end) g1,  --合格次数
    count(case when ksdc = 'N' then 1 else null end) g2  --不合格次数
  from a
  group by nsid
) x on m.nsid = x.nsid
left join
(select nsid,
    count(case when ksdc = 'Y' then 1 else null end) g1,  --合格次数
    count(case when ksdc = 'N' then 1 else null end) g2  --不合格次数
  from b
  group by nsid
) y on m.nsid = y.nsid
left join
(select nsid,
    count(case when ksdc = 'Y' then 1 else null end) g1,  --合格次数
    count(case when ksdc = 'N' then 1 else null end) g2  --不合格次数
  from c
  group by nsid
) z on m.nsid = z.nsid

Answer 7

47522341真是好兄弟，热心人品好技术高，结贴了还解惑。
不知兄弟客户端常使用什么工具，本人喜欢delphi