mysql - 我应该如何在mysql表设计中存储问题和答案

Question

我有下表

1. 用户
2. 问题
3. 问题类别
4. 答案

现在我很困惑我的用户类是否应该有任何问题链接或只是答案

用户将回答表单上显示的问题。

所以每年不同的问题会有不同的答案

Answer 1

我假设一个用户可以提出一个问题，一个问题可以属于一个类别，并且多个用户可以发布答案。实际上，它是关于像 stackoverflow 这样的网站是什么样的。

create table user (
  user_id integer primary key,
  name varchar(40) not null
);

create table category (
  category_id integer primary key,
  category varchar(40) not null
);

create table question (
  question_id integer primary key,
  question text,
  asked_by_id integer not null,
  category_id integer not null,
  foreign key asked_by_id references user(user_id),
  foreign key category_id references categor(category_id)
);

create table answer (
  answers_id     integer not null,
  answered_by_id integer not null,
  answer text,
  primary key (answers_id, answered_by_id), -- we allow one answer per person for a question
  foreign key answers_id references question(question_id),
  foreign key answered_by_id references user(user_id)
);

然后查询:

select qu.name as asked_by,    question, category,
       au.name as answered_by, answer
from user qu                                       -- iterating through users
join question q on qu.user_id    = asked_by_id     -- joining them to their questions
join category c on c.category_id = q.category_id   -- find the category
join answer     on answers_id    = question_id     -- join by what answers this question
join user au    on au.user_id    = answered_by_id; -- looking up the user who answered it

(我没有在服务器上试过，所以可能会有错别字，欢迎指正。)