Logical database Design-multi-column properties (multi-column career)

Suppose there is one to develop a question system, all of which are indefinite choice questions. A question may have 2,3,4 ... an answer, how should the data be designed? The purpose of the Department is to explain the problem, such as the same problem as the storage phone, a person may have multiple numbers and so on.

One, store multi-valued properties

Anti-pattern: Create multiple columns.

We know that each column is best to store only one value, so let's look at the following design:

 　　 CREATE TABLEQuestion (QuestionIDintPK, Questionbodynvarchar( -), Answer1nvarchar( -), Answer2nvarchar( -), Answer3nvarchar( -), Answer4nvarchar( -))

Similar to the above design, assuming that the answer is only two, then the following two answer3,answer4 is null, the display table is as follows:

　　

This is a very traditional property design, which makes the task even easier now!

　　1. Query data

Suppose there's an error in the answer to a question, but you only remember the answer.

SELECT *  fromQuestionWHEREAnswer1= 'pigs have 4 legs .'    ORAnswer2= 'pigs have 4 legs .'    ORAnswer3= 'pigs have 4 legs .'    ORAnswer4= 'pigs have 4 legs .'

The results appear as follows:

　　

If, in addition, there is a problem, there is an answer to the pig has 3 legs, then you have to find this data, you need this:

SELECT *  fromQuestionWHERE(Answer1= 'pigs have 4 legs .' ORAnswer2= 'pigs have 4 legs .' ORAnswer3= 'pigs have 4 legs .' ORAnswer4= 'pigs have 4 legs .')     and(Answer1= 'pigs have 3 legs .' ORAnswer2= 'pigs have 3 legs .' ORAnswer3= 'pigs have 3 legs .' ORAnswer4= 'pigs have 3 legs .')

How long and smelly it is! Not afraid, the elder brother comforted you, actually has the simple point method:

SELECT *  from WHERE     ' pigs have 3 legs . ' inch (answer1,answer2,answer3,answer4)    　　  and    ' pigs have 4 legs . ' inch (ANSWER1,ANSWER2,ANSWER3,ANSWER4)　　　　

How short a lot of it, I believe you will still not sell the account.

　　2. Adding, updating, and deleting values

In the above design, suppose I want to delete the answer ' pig has 3 legs ' SQL statement How to write it? Everybody's got the test.

UPDATEQuestionSETAnswer1= Nullif(Answer1,'pigs have 3 legs .'), Answer2= Nullif(Answer2,'pigs have 3 legs .'), Answer3= Nullif(Answer3,'pigs have 3 legs .'), Answer4= Nullif(Answer4,'pigs have 5 legs .')WHEREQuestionID= 2

Add an answer, update an answer is a bit difficult to write, interested friends can knock on their own.

　　3. To ensure uniqueness

How to ensure that the same value does not appear in more than one column, it is possible that you do not want two answers to be the same in a question. It is difficult to prevent duplicate answers from appearing because unique can only be used for rows.

　　4, the value is growing

When we have 5 answers to a question, tragedy, you have to change the table structure.

The above-mentioned problems show that the above design is simply fragile.

Ii. Solutions-dependent tables

The root of the problem is storing a property with multiple values. For the above design, if each problem is stipulated to be 4 answers, the above design can be used. The problem is that the number of answers is uncertain.

Therefore, we need to create a subordinate table that extracts an indeterminate number of values as a row store instead of a column.

The overall design is as follows:

CREATE TABLEQuestion (QuestionIDintPK, Questionbody Bodynvarchar( -))CREATE TABLEAnswer (AnsweridintPK, Answerbodynvarchar( -), QuestionIDint,    FOREIGN KEY(QuestionID)REFERENCESQuestion (QuestionID))

For the above design, how many answers are no problem, and the addition and deletion of the search and change are simple more than a grade.

Actually, it's just a one-to-many relationship. And this problem is easy to see at a glance, but the variant of the problem you may not be conditional on the reaction of the same subordinate table.

Logical database Design-multi-column properties (multi-column career)