How should we design a database (3) (continued) (1)

After writing the previous blog "how to design a database (iii)", I deeply felt that I was not clearly written. It took a lot of space to describe the problem, however, the solution to the problem and its advantages and disadvantages were taken over by us, so we wrote this blog to apologize.

Download Sample Code: Click here to download

The sample code is described below

First, let's review the problems described in "how to design a database (3:

Now there is a system. We assume it is a course selection system.

The system should be 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?

There are roughly three solutions

1. Adding redundancy to tables

2. Add redundant tables

3. model inheritance

First thought:Redundancy is added to the table, which has been analyzed in the previous article. The advantages and disadvantages are described here.

The advantage is simplicity: simple thinking and easy implementation

The disadvantage is that maintenance is difficult: ① if our system sells many schools, it is a nightmare for system maintenance personnel. ② violation of open and closed: every time you add a field, you have to modify the existing Model.

The second approach:Add redundant tables

As Xu shaoxia said in the previous article, we can also design it like this and use a redundant table to store different fields ,:

FirstName, LastName, and Number correspond to the three rows in the extended table.

The advantage is that the idea is simple and the implementation is more complicated than the first one, but not complicated.

Disadvantages: ① Join.

② Open or closed: Modify the existing Model for each field added.

Xu shaoxia also said the disadvantage in his message: it is hard to query data. It can only be used in areas with low read/write pressure .)

The third approach:Model inheritance

This method is suitable for ORM or Code First ORM.

Here, the recommended EF5EF5 environment is. NET 4.5, But. NET 4 is also harmless)

 
 

  
  
public class Identifier  
  
  
    {  
  
  
        [Key]  
  
  
        public int ID { get; set; }  
  
  
    }  
  
  
 
  
  
    public interface Contact  
  
  
    {  
  
  
        string Phone { get; set; }  
  
  
        string Email { get; set; }  
  
  
    }  
  
  
 
  
  
   public class TeacherBase : Identifier, Contact  
  
  
    {  
  
  
        [StringLength(50)]  
  
  
        public string UserName { get; set; }  
  
  
 
  
  
        [StringLength(50)]  
  
  
        public string Pwd { get; set; }  
  
  
 
  
  
        [StringLength(50)]  
  
  
        public string Phone { get; set; }  
  
  
 
  
  
        [StringLength(50)]  
  
  
        public string Email { get; set; }  
  
  
    } 
 
 

This is our basic Teacher table.

Then our system is sold to school A. The Teacher table of school A has two different fields: FirstName and LastName. The Model should be written in this way.

 
 

  
  
namespace Model.A  
  
  
{  
  
  
    public class Teacher : TeacherBase  
  
  
    {  
  
  
        [StringLength(50)]  
  
  
        public string FirstName { get; set; }  
  
  
 
  
  
        [StringLength(50)]  
  
  
        public string LastName { get; set; }  
  
  
    }  
  
  
} 
 
 

Generated database

Then the system was sold to B. B's school system is deployed independently. The Difference Field in the Teacher table of school B is Number:

 
 

  
  
namespace Model.B  
  
  
{  
  
  
    public class Teacher : TeacherBase  
  
  
    {  
  
  
        [StringLength(50)]  
  
  
        public string Number { get; set; }  
  
  
    }  
  
  
} 
 
 

Generated database