Learn MySQL (2)-How to design a database (i)

In learning MySQL (1) learned some basic operations.

Next a more important question in front of me, assuming now to combat, of course, to create a database, but the database will generally involve a lot of tables, if the design is inappropriate, some confusion logically, then such a database will certainly not be useful.

In the beginning to learn this thing, feeling is excl, nothing great ah. Get one or two tables. But if we use a table for whatever we do, we may encounter problems. In the learning MySQL book, a good example is given to illustrate why multiple tables are used:

Suppose a school needs to record the results of every student in the school, then it needs to record the student's name, course, grade. The table you are building is probably:

But sometimes the student's name is the same, for example, in the table above, two Susan Smith have taken the computing mathematics course, so which one does it correspond to which student? So we also need to add a unique identifier to the table, such as the number, at this point the table is as follows:

At this point, we also found that the table has a classmate to take the two computing mathematics course exam, then these two courses is the student when the test? So we also need to add school year, semester. This time table is as follows:

At this time, this table seems to be no problem, can meet the record of each student's performance, should say can be satisfied. But if you look closely you will find out, how many times the name of the student appeared, so it is not a waste of very funny storage it? Actually just need to study number on the line. Then we thought about the student information and the individual storage. This will have two tables:

Table 1 Student Information:

Table 2 Exam Results information:

It seems to be more comfortable at this time.

In fact, through the above example, we can also record the course separately a table, and the examination information and record a table. This is the equivalent of seeing the student as an entity, and the course as an entity, and the test result is the relationship of two entities, the well-known ER model (the Entity Relationship model).

So much has not yet been said about how to design a database. So how do you design it? In fact, we can see some shadows from the above example. For example, the first need to figure out what database to design, that is, to clarify the requirements, the above example from the beginning to the back to add the number of information, add the school year semester information, etc. , and then the conceptual problem, what is the entity what is the relationship, and finally the logical design , which is close to creating the table.

Learn more in more detail on learning MySQL (2)-How to design data (ii)

Learn MySQL (2)-How to design a database (i)