一个变态的排列组合的数据库设计

Question

Dear All:
      大家好！最近设计数据库时，被一变态需求困扰许久，百思不得其解。故在此跪求各位不吝赐教！表格1  订单调查                 问题1     数量-订单数
                                A1) 10            A2) 15               A3) 20          A4)>20
                 问题2     数量+订单数                        
                                  B1) 100    B2) 150      B3) 200         
                 问题3     数量/订单数
                                  C1) 0.1            C2) 0.15     C3) 0.2        C4)0.25     C5)0.3     C6)>0.3
                 问题4     数量*订单数
                        .....
                 问题N    
上面是一份问题调查的表格。两点需说明：
  1、表格包括N个问题(问题1、问题2..问题N)   
  2、问题包括N个答案(A1..A4、B1..B3、C1..C6、N1..NN)用户可在一输入界面选择 任意问题和答案 进行排列组合。每种组合的结果，作为表格的一种评分标准。类似如下图：
                                         表格 订单调查         结果：准时                         
                                                 问题                             答案      
                                                 问题1                             A1                                        
                                                 问题1                             A3                                         
                                                 问题2                             B2                                  
                                                 问题2                             B3                                        
                                                 问题3                             C1                                        该关系表示：(第一题选了A1 或 A3) 且 (第二题 选了 B2 或 B3) 且 ( 第三题 选了C1 ) 的话，则表格评分为(准时)[b] [/b]                                               表格 订单调查         结果：一般         
                
                                                问题                            答案      
                                                问题1                            A1                                        
                                                问题1                            A3                                         
                                                问题2                            B2                                                                                   
                                                问题4                            D4                                                                                         
                                                 
该关系表示：(第一题选了A1 或 A3) 且  (第二题 选了 B2 或 B4) 且 (第四题 选了D4) 的话 ,则表格评分为(一般)
                                         表格 订单调查         结果：不准时                 
                                                问题                            答案      
                                                问题1                            A1                                        
                                                问题1                            A2                                         
                                                问题2                            B1                                  
                                                问题2                            B4                                        
                                                问题3                            C1                                        
                                                问题3                            C5                                         该关系表示：(第一题选了A1 或 A2 ) 且 (第二题 选了 B1 或 B4) 且 (第三题 选了C1 或 C5) 的话,则表格的标准为(不准时)                用户录入上述3个评分标准后，即将3条记录保存到一个中间表中。请教大家，我该如何设计这个关系呢？

Answer 1

没明白LZ想说什么，把问题和答案还有结果作为三个字段不行吗？再加个用户ID和评分标准序号作为每一种选择的标识。

Answer 2

  问题   答案  评价
  问题1   A1  准时
  ..........
   问题1 A1   一般
  ...........
  问题1 A1     不准时
  ..............
往中间表插入了评分标准如上
例如用户回答答案如下
  问题 答案   
  问题1 A1   
  问题1 A3   
  问题2 B2   
  问题4 D4  
则选出相应的评价,
问题1 A1 的评价为三个都有,但是4个评价一样的只有一组
4个评价一样的即为他的回答评价

Answer 3

写一个供参考：成绩评价表
人员id,人员评价答案表
人员id,问题号,答案评定标准表
问题号,答案,评价
--更新人员评价
UPDATE 成绩评价表 c
   SET c.人员评价 =  SELECT b.评价
                   FROM (SELECT 人员id, wm_concat(答案) da FROM 答案表 GROUP BY 人员id ORDER BY 答案) a,
                        (SELECT wm_concat(答案) da, 评价 FROM 评定标准表 GROUP BY 评价 ORDER BY 答案) b
                  WHERE a.da = b.da AND
                        a.人员id = c.人员id

Answer 4

已经解决问题。
题目：
CREATE TABLE questions (
   question_id NUMBER PRIMARY KEY
  ,text        VARCHAR2(50)       --- 问题内容
   );答案：
CREATE TABLE answers (
   question_id NUMBER
  ,answer_num  NUMBER         ----- 如果有四个答案就编号1-4
  ,text        VARCHAR2(20)   ---- 答案内容
  ,PRIMARY KEY (question_id,answer_num)
   );问卷：
CREATE TABLE surveys (
   survey_id   NUMBER PRIMARY KEY
  ,survey_name VARCHAR2(50)
  );评分标准: 
CREATE TABLE criteria (
   survey_id   NUMBER           
  ,result      VARCHAR2(10)     ----- 一般，准时等等
  ,question_id NUMBER        
  ,answer_num  NUMBER           
  ,PRIMARY KEY (survey_id,result,question_id,answer_num)
  ,FOREIGN KEY (question_id,answer_num) REFERENCES answers(question_id,answer_num)
  ,FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) 
   );答卷：
CREATE TABLE submitted_answers (
   survey_id   NUMBER           
  ,customer_id NUMBER      
  ,question_id NUMBER
  ,answer_num  NUMBER
  ,PRIMARY KEY (survey_id,customer_id,question_id)  ---- 每个问题每人只能提供一个答案
  ,FOREIGN KEY (question_id,answer_num) REFERENCES answers(question_id,answer_num)
  ,FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) 
   );
通过以下sql查看某个用户提交的调查属于哪个评分标准：我修改如下：
   SELECT a.survey_id,a.customer_id,a.cnt,c.result
   FROM (select a.*,count(question_id) over (partition by survey_id,customer_id) cnt
   from submitted_answers a) a,criteria c
  WHERE a.survey_id = c.survey_id
  AND a.question_id = c.question_id
  AND a.answer_num = c.answer_num
  GROUP BY a.survey_id,a.customer_id,a.cnt,c.result
  having count(*) = a.cnt;