I need help with a database table called "Question". I want to know which layout is better out of the two below when it comes to primary keys:
我需要一个叫做“问题”的数据库表的帮助。我想知道,当涉及到主键时,下面两种布局中哪一种更好:
Question Table method 1:
问题表方法1:
QuestionId (int 3) PK
SessionId (varchar10) PK
QuestionContent (varchar800)
NoofAnswers (int 3)
AnswerId (int 5) Auto Increment
Marks (int 3)
In the above table, QuestionId and SessionId are PK (primary Key), AnswerId is not PK, but it is auto increment.
在上表中,QuestionId和SessionId是PK(主键),AnswerId不是PK,而是自动递增。
Question Table method 2:
问题表方法2:
QuestionId (int 3)
SessionId (varchar10)
QuestionContent (varchar800)
NoofAnswers (int 3)
AnswerId (int 5) Auto Increment PK
Marks (int 3)
In the table above, only AnswerId is PK and autoincrement. But if if I do that, it means other tables which contains QuestionId will have to link to this table's QuestionId as non keys or something like that.
在上面的表格中,只有AnswerId是PK和自动递增。但是如果我这样做,它意味着包含QuestionId的其他表必须以非键或类似的方式链接到该表的QuestionId。
The database is not letting me do PK for QuestionId, SessionId and AnswerId as its saying that AnswerId is auto increment so no need for any other primary keys.
数据库不允许我对QuestionId、SessionId和AnswerId执行PK,因为它说AnswerId是自动递增的,因此不需要任何其他主键。
2 个解决方案
#1
3
I think both cases are not good! You are giving PK's a meaning, but PK's should have no meaning.
我认为这两种情况都不好!你给PK的意思,但是PK应该没有意义。
Just make your PK a int(11) Auto Increment
and link tables together with a foreign key.
只需将您的PK设置为int(11)自动递增,并将表与外键链接在一起。
Update
更新
Each table has its own PK. The Questions table has a QuestionId PK.
每个表都有自己的PK。问题表有一个QuestionId PK。
Tying table together is based on the relation that tables have. There are different scenarios for 1:1
relations, 1:many
relations and many:many
relations.
将表连接在一起是基于表之间的关系。1:多关系,多关系,多关系,多关系。
Example:
例子:
CREATE TABLE `Questions` (
`QuestionId` int(11) NOT NULL AUTO_INCREMENT,
`AnswerId` int(11) DEFAULT NULL,
PRIMARY KEY (`QuestionId`),
KEY `FK_Answer` (`AnswerId`),
CONSTRAINT `FK_Answer` FOREIGN KEY (`AnswerId`) REFERENCES `Answers` (`AnswersId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `Answers` (
`AnswerId` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`AnswerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Looking from the Questions there can only be 1 answer on a question (1:1
). Looking from a answer, it can be used for many questions (1:n
). For a many:many
relations you need a extra table.
从问题来看,一个问题只有一个答案(1:1)。从答案来看,它可以用于许多问题(1:n)。对许多人来说:许多关系需要一个额外的表。
Here is a youtube doing some extra explanation: http://www.youtube.com/watch?v=RXOj0D80kRg
这里有一个youtube做一些额外的解释:http://www.youtube.com/watch?v=RXOj0D80kRg
#2
0
Normalized:
归一化:
table sessions: id
表会议:id
table questions: id (int 3) auto increment pk session_id varchar255 QuestionContent (varchar800) Marks (int 3)
表问题:id (int 3)自动递增pk session_id varchar255问题内容(varchar800)标记(int 3)
table answers: id auto increment pk question_id (references questions(id)) content varhcar(255)
表答:id自动递增pk question_id(引用问题(id))内容varhcar(255)
#1
3
I think both cases are not good! You are giving PK's a meaning, but PK's should have no meaning.
我认为这两种情况都不好!你给PK的意思,但是PK应该没有意义。
Just make your PK a int(11) Auto Increment
and link tables together with a foreign key.
只需将您的PK设置为int(11)自动递增,并将表与外键链接在一起。
Update
更新
Each table has its own PK. The Questions table has a QuestionId PK.
每个表都有自己的PK。问题表有一个QuestionId PK。
Tying table together is based on the relation that tables have. There are different scenarios for 1:1
relations, 1:many
relations and many:many
relations.
将表连接在一起是基于表之间的关系。1:多关系,多关系,多关系,多关系。
Example:
例子:
CREATE TABLE `Questions` (
`QuestionId` int(11) NOT NULL AUTO_INCREMENT,
`AnswerId` int(11) DEFAULT NULL,
PRIMARY KEY (`QuestionId`),
KEY `FK_Answer` (`AnswerId`),
CONSTRAINT `FK_Answer` FOREIGN KEY (`AnswerId`) REFERENCES `Answers` (`AnswersId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `Answers` (
`AnswerId` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`AnswerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Looking from the Questions there can only be 1 answer on a question (1:1
). Looking from a answer, it can be used for many questions (1:n
). For a many:many
relations you need a extra table.
从问题来看,一个问题只有一个答案(1:1)。从答案来看,它可以用于许多问题(1:n)。对许多人来说:许多关系需要一个额外的表。
Here is a youtube doing some extra explanation: http://www.youtube.com/watch?v=RXOj0D80kRg
这里有一个youtube做一些额外的解释:http://www.youtube.com/watch?v=RXOj0D80kRg
#2
0
Normalized:
归一化:
table sessions: id
表会议:id
table questions: id (int 3) auto increment pk session_id varchar255 QuestionContent (varchar800) Marks (int 3)
表问题:id (int 3)自动递增pk session_id varchar255问题内容(varchar800)标记(int 3)
table answers: id auto increment pk question_id (references questions(id)) content varhcar(255)
表答:id自动递增pk question_id(引用问题(id))内容varhcar(255)