如何规范化sql查询的结果，以及如何将一个外键引用到两个列

Question

我有以下表格:

person table

 +-----------+-------------+------+-----+---------+-------+
    | Field     | Type        | Null | Key | Default | Extra |
    +-----------+-------------+------+-----+---------+-------+
    | person_id | int(100)    | NO   | PRI | NULL    |       |
    | name      | varchar(50) | NO   |     | NULL    |       |
    +-----------+-------------+------+-----+---------+-------+
    +-----------+---------------+
    | person_id | name          |
    +-----------+---------------+
    |         1 | linon jacob   |
    |         2 | andrew simons |
    |         3 | john random   |
    |         4 | kayne ran     |
    +-----------+---------------+

EDIT:
As per the below comment:
    create table person(person_id int(100) primary key not null, name varchar(50) not null);
    create table questions(QID int(50) primary key not null, questions varchar(100));
    create table custom_questions(CID int(20) not null, questions varchar(50) not null, PID int(20), primary key (CID), foreign key (PID) references person(person_id));
    create table feedback_system(surveyID int(20) not null, recepient varchar(50), questionID int(20) not null, submitter name varchar(50), response varchar(10), primary key (surveyID, questionID));

Answer 1

我相信您需要使用一些左连接，对于每个反馈行，您要么在questions中有问题，要么在custom_questions中有问题。

像这样:

SELECT IFNULL(q.questions, cq.questions) as 'question',
   f.recepient_name, 
   f.submitter_name,
   f.response
FROM feedback_system f
LEFT JOIN custom_questions cq
    ON cq.CID = f.questionID    
LEFT JOIN questions q
    ON q.QID = f.questionID 
WHERE f.surveyID = 1;

Answer 2

您的查询至少有两个问题。一个是您实际上在questions和custom_questions之间进行交叉连接，导致行重复。另一个问题是，survey_ID上的过滤只针对自定义问题，因为and的优先级高于or。

编写查询的一种方法是使用UNION ALL来组合两个问题表的结果:

SELECT q.questions, feedback_system.recepient_name, feedback_system.submitter_name
FROM questions q
INNER JOIN feedback_system
ON q.QID = feedback_system.questionID
WHERE surveyID = 1
UNION ALL
SELECT cq.questions, feedback_system.recepient_name, feedback_system.submitter_name
FROM custom_questions cq
INNER JOIN feedback_system
ON cq.CID = feedback_system.questionID
WHERE surveyID = 1

另一种可能性是将所有问题放在一个单独的表'questions'中，而将自定义问题的附加信息放在一个单独的表'custom_question_info'中。