How to design a database (3)

In "How to Design Database 2)", 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 in the blog, Man and Woman have only four different attributes: 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, a 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:

Well, 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. The total number of tables to be maintained is 30.) * 30 redundancy = 900 differential fields.

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's own consideration.

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. For details, see the previous article.) Select the appropriate database and its corresponding Model through the configuration file.

Yes, this method is good, the only disadvantage is probably relying on ORM -- Use ORM to generate database, and T-SQL statements.

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.

By the way, attach a small test: In. in the. net 4 Environment, data records are inserted, including EF5, nhib.pdf, and ADO.. Net to SQL server 2008, 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 in batch at a time). Other methods are single inserts. nhib.pdf disables the first-level cache;

MongoDB uses the insert method of "Release arrow.

MongoDB uses a secure insert mode without data loss ).

Link: http://www.cnblogs.com/CrazyJinn/archive/2012/09/25/2701284.html