How to design a database (3)

The first is nonsense:

Previous Article:

How should we design a database? (1)

How should we design a database? (2)

 

In "how to design a database (ii)", Jack London Chen raised some questions, which are roughly as follows:

"Man/woman should be designed in the same table. Most user tables are designed as one table. Neither administrator nor user can be connected ."

I think it's still because my example is too casual, because there are only four different attributes of man and woman in the blog: hascar \ hashouse \ hasmoney, and isbeauty

In fact, I can't tell you what to say about this problem. Let's simply say: we assume that a man user is implementing a marriage system, while a woman user is implementing a beauty system. In this case, we should be able to understand why man and woman cannot be in the same table.

 

After talking nonsense, the text begins.

/* ===================================================== ======================================= */

Now there is a system. We assume it is a course selection system. There are two types of users, teacher and student, and a curriculum table is the course summary table to store what courses the school has and what credits each course has. Then a teacher, one course and multiple students can start the class.

The table structure is as follows:

 

 

 

The logic is simple and clear at a glance

But the problem is that our systems are sold by school. The course selection logic of each school is the same, while the data in the table is common but also different. For example, the basic structure of the teacher table is as follows:

Now we sell the system to school. In addition to the username and password, the teacher table in school a also stores the firstname and lastname of the instructor. The table structure changes as follows:

School B also buys our system. Their teacher table should not be firstname or lastname, but should store the instructor's employee ID "Number". The table structure is as follows:

Okay, now we have a problem: how can we solve this difference?

The simplest way is to add redundant fields to a table. For example, design a table as follows:

If our system only sells two or three schools, this is feasible. However, for example, if our system sells 30 schools and each school has its own differential field, this table requires 30 redundant fields to deal with this difference. Not to mention that the system needs to be changed every time redundancy is added, not to mention that redundancy is a waste of space to reduce transmission efficiency, but to maintain such redundancy, I already think it is a disaster: The teacher table has different fields, other tables are also available. Suppose there are 60 tables in a medium-sized system, 30 of which are physical tables and 30 are Relational Tables. So a total of 30 (number of tables) x 30 (number of redundancy) = 900 differential fields need to be maintained

 

The second idea is to create a redundant table to store the differences. In fact, this is the same as adding redundancy to tables, so we don't need to analyze it more and leave it to everyone to think about.

 

The third idea is to create different databases. Actually, the databases in every school are different ...... The table in the database of school a stores the unique fields of school A and the unique fields of school B. There is no relationship between the two, and then the model is designed with the idea of L inheritance (see the previous article), through the configuration file to select the appropriate database and its corresponding model

Yes, this method is quite good, the only drawback is probably relying on ORM -- Use ORM to generate database, and T-SQL statement

 

If you are a serious fan of relational databases, this article is over, and the following will not be appetizing for you.

 

/* ===================================================== ======================================= */

As we all know, because reflection is widely used, the efficiency of ORM is not so high, and the scalability of relational databases is not so good.

As a radical developer, I have always wanted to try nosql in projects

In the following article, I will explain how to use MongoDB to solve the differences described above.

 

Two articles are recommended for getting started with MongoDB:

MongoDB development and learning (1) getting started with a classic

MongoDB Development in Practice [no basic learning, with a complete Asp.net example]

 

Append a small test: 2008 pieces of data are inserted in the. NET 4 environment, namely EF5, nhib.pdf, and ADO. Net to SQL Server, and the MongoDB official driver to MongoDB.

EF Duration: 00: 00: 25.4972758

Ado. Net time consumption: 00: 00: 23.8307860

Nhibernate time: 00: 00: 26.0199898

MongoDB time consumed: 00: 00: 01.9474134

Here, EF inserts 1000 pieces of data (batch insert) each time. Other methods are single insert; nhib.pdf disables the first-level cache;

MongoDB uses the insert method of "Release arrow"

MongoDB uses a secure insert mode (data will not be lost)

/* ===================================================== ======================================= */

 

 

PS: in flash memory, I said I had revealed my photos in my blog post. However, because this article was written in the company, let's wait for the next article.

PS: 10 · 1 now