Turn: How should we design the database?

How to design databases has always been a matter of benevolence and wisdom.

For a database design, it is not easy to distinguish between good and bad. Maybe the sentence is the best, but it is the most suitable. When discussing a database design, we should discuss it in a specific demand environment.

 

 

Next we will discuss the storage of user contact information frequently encountered in projects.

Here, I will use the classification method of "ordinary -- Literature and Art -- two forces" popular on the Internet to describe the three database design ideas I mentioned below, in addition, by querying data (adding, deleting, and modifying data, the code costs of the three designs are almost the same) and the database is subject to changes in requirements, we can think about the advantages and disadvantages of these three designs.

 

Ordinary youth:

Maybe we have all designed databases like this.

Student table tb_Student:

Name	Varchar (100)	Name
Telphone	Varchar (200)	Contact number
Email	Varchar (200)	You know
Fax	Varchar (200)	Fax

This should be the easiest way of thinking, simple and clear.

For example, if you want to query the contact information of a person, you can use only one statement:

Select Name, Telphone, Email, Fax from table where Condition

During the query, the database design is very clear and there is no difficulty in thinking and no logical challenge. However, when the demand changes, it will be a disaster.

For example, you need to add a new type of user: Principal. So what should we do?

The answer is: Add a table tb_Headmaster.

In fact, adding a table is not much modified because we do not need to modify the storage logic of the student table at all. In other words, this design follows the principle of opening and closing.

But if students want to add a contact method HomePhone, a disaster occurs.

What should I do?

Add a HomePhone column in tb_Student? This means that at least the entire Model layer (or DAL layer) needs to be modified, which is huge and prone to errors.

Or create another table tb_Student2 to store the HomePhone and associate the two tables with IDs? According to the scale of changes, such changes are relatively simple and not prone to errors, but will increase the logic cost in future maintenance. Your program becomes incomprehensible when you respond to demand changes in this way.

 

 

Literary youth:

UserRole	Int	Corresponding user type (None = 0, Student = 1, Teacher = 2, Headmaster = 4)
OwnerID	Int	Corresponding user ID
ContactMethod	Int	Contact information (None = 0, Email = 1, HomePhone = 8, WorkPhone = 16, MobilePhone = 32, Fax = 64)
ContactInfo	Varchar (255)	Contact information

This is a many-to-many relationship. When we want to query the contact information of a user, it is a logical catastrophe:

Select ContactInfo from table where UserRole = user type and OwnerID = user ID

This method extracts all the contact information of a user, including Email, HomePhone, and WorkPhone. Then, we can determine the ContactMethod type in the program and differentiate the specific contact information. You can simply think of using the switch-case statement, like this:

Var contact = all contact information of the user obtained from the preceding SQL statement; foreach (var item in contact) {switch (item. contactMethod) {case ContactMethod. workPhone: txtWorkPhone. text = item. contactInfo; break; case ContactMethod. email: txtEmail. text = item. contactInfo; break; case ContactMethod. fax: txtFax. text = item. contactInfo; break; case ContactMethod. otherPhone: txtOtherPhone. text = item. contactInfo; break; case ContactMethod. mobilePhone: txtMobilePhone. text = item. contactInfo; break ;}}

Of course, you can also try the following method. I personally think this method is more elegant.

Var contact = all contact information of the user obtained from the preceding SQL statement; txtWorkPhone. text = (from a in contact where. contactMethod = ContactMethod. work_Phone select. contactInfo ). toString ();
// And so on, you know

 

Note: do not try to use statements similar to the following to query the contact information of a user:

Select ContactInfo from table where UserRole = a user type and OwnerID = a user ID and ContactMethod = 1 // retrieve a user's Emailselect ContactInfo from table where UserRole = a user type and OwnerID = a user ID and ContactMethod = 8 // retrieve a user's HomePhone

Believe me, this method is very stupid: whenever you want to retrieve a user's contact information, you need to establish a connection with the database, open/close the database once; this approach is costly. Even if there is a database connection pool, even if there is a database cache, this stupid approach should be avoided.

 

Well, after using so much code, I finally found a user's contact information. Anyway, I personally think this design method is a logical catastrophe during queries. What? You said you enjoyed it? Well, it seems that my brain capacity is not enough ......

However, when we are faced with changes in demand, we are very happy.

What, add a type of user? Simple: add an enumeration to UserRole.

What, add a contact method? If ContactMethod is added with an enumeration, it will be OK.

When using this table design, I believe you will smile and face the changes in demand.

 

 

Second force youth

Yesterday, I discussed this issue with my colleagues. According to his statement, I threw a field into the table to store the json

Contact

Varchar (8000)

Used to store json

For example, there is a user:

ID: 1

Name: James

Telphone: 1234

Email: 123@123.com

Fax: 5678

The database is saved as follows:

[{"ID": 1, "Name": "Zhang San", "Telphone": "1234", "Email": "123@123.com", "Fax ": "5678"}]

When I heard of this design idea, the tiger body was slightly shocked: depend, this is all right. According to this design, all my tables are saved in a json file. But think about it after being shocked. In fact, this design is also desirable.

First of all, in terms of queries, like ordinary youth, only one SQL statement is required:

Select Contact from table where Condition

After the query, you can use the json processing function to retrieve the desired data.

 

What will happen when the demand changes:

When adding a type-1 user, you need to add a table. It is also in line with the open and closed principle and the original code has not been changed

Add a contact method to store more things only when saving json.

 

However, this design requires a little effort to Update a piece of data: query a piece of data first, reorganize the json, and then Update

 

 

I have written so much that I hope I can clearly express the problems I want to express. Hope haihan is missing, and you are welcome to leave a message

The third method is novel and creative.

How to design databases has always been a matter of benevolence and wisdom.

For a database design, it is not easy to distinguish between good and bad. Maybe the sentence is the best, but it is the most suitable. When discussing a database design, we should discuss it in a specific demand environment.

 

 

Next we will discuss the storage of user contact information frequently encountered in projects.

Here, I will use the classification method of "ordinary -- Literature and Art -- two forces" popular on the Internet to describe the three database design ideas I mentioned below, in addition, by querying data (adding, deleting, and modifying data, the code costs of the three designs are almost the same) and the database is subject to changes in requirements, we can think about the advantages and disadvantages of these three designs.

 

Ordinary youth:

Maybe we have all designed databases like this.

Student table tb_Student:

Name	Varchar (100)	Name
Telphone	Varchar (200)	Contact number
Email	Varchar (200)	You know
Fax	Varchar (200)	Fax

This should be the easiest way of thinking, simple and clear.

For example, if you want to query the contact information of a person, you can use only one statement:

Select Name, Telphone, Email, Fax from table where Condition

During the query, the database design is very clear and there is no difficulty in thinking and no logical challenge. However, when the demand changes, it will be a disaster.

For example, you need to add a new type of user: Principal. So what should we do?

The answer is: Add a table tb_Headmaster.

In fact, adding a table is not much modified because we do not need to modify the storage logic of the student table at all. In other words, this design follows the principle of opening and closing.

But if students want to add a contact method HomePhone, a disaster occurs.

What should I do?

Add a HomePhone column in tb_Student? This means that at least the entire Model layer (or DAL layer) needs to be modified, which is huge and prone to errors.

Or create another table tb_Student2 to store the HomePhone and associate the two tables with IDs? According to the scale of changes, such changes are relatively simple and not prone to errors, but will increase the logic cost in future maintenance. Your program becomes incomprehensible when you respond to demand changes in this way.

 

 

Literary youth:

UserRole	Int	Corresponding user type (None = 0, Student = 1, Teacher = 2, Headmaster = 4)
OwnerID	Int	Corresponding user ID
ContactMethod	Int	Contact information (None = 0, Email = 1, HomePhone = 8, WorkPhone = 16, MobilePhone = 32, Fax = 64)
ContactInfo	Varchar (255)	Contact information

This is a many-to-many relationship. When we want to query the contact information of a user, it is a logical catastrophe:

Select ContactInfo from table where UserRole = user type and OwnerID = user ID

This method extracts all the contact information of a user, including Email, HomePhone, and WorkPhone. Then, we can determine the ContactMethod type in the program and differentiate the specific contact information. You can simply think of using the switch-case statement, like this:

Var contact = all contact information of the user obtained from the preceding SQL statement; foreach (var item in contact) {switch (item. contactMethod) {case ContactMethod. workPhone: txtWorkPhone. text = item. contactInfo; break; case ContactMethod. email: txtEmail. text = item. contactInfo; break; case ContactMethod. fax: txtFax. text = item. contactInfo; break; case ContactMethod. otherPhone: txtOtherPhone. text = item. contactInfo; break; case ContactMethod. mobilePhone: txtMobilePhone. text = item. contactInfo; break ;}}

Of course, you can also try the following method. I personally think this method is more elegant.

Var contact = all contact information of the user obtained from the preceding SQL statement; txtWorkPhone. text = (from a in contact where. contactMethod = ContactMethod. work_Phone select. contactInfo ). toString ();
// And so on, you know

 

Note: do not try to use statements similar to the following to query the contact information of a user:

Select ContactInfo from table where UserRole = a user type and OwnerID = a user ID and ContactMethod = 1 // retrieve a user's Emailselect ContactInfo from table where UserRole = a user type and OwnerID = a user ID and ContactMethod = 8 // retrieve a user's HomePhone

Believe me, this method is very stupid: whenever you want to retrieve a user's contact information, you need to establish a connection with the database, open/close the database once; this approach is costly. Even if there is a database connection pool, even if there is a database cache, this stupid approach should be avoided.

 

Well, after using so much code, I finally found a user's contact information. Anyway, I personally think this design method is a logical catastrophe during queries. What? You said you enjoyed it? Well, it seems that my brain capacity is not enough ......

However, when we are faced with changes in demand, we are very happy.

What, add a type of user? Simple: add an enumeration to UserRole.

What, add a contact method? If ContactMethod is added with an enumeration, it will be OK.

When using this table design, I believe you will smile and face the changes in demand.

 

 

Second force youth

Yesterday, I discussed this issue with my colleagues. According to his statement, I threw a field into the table to store the json

Contact

Varchar (8000)

Used to store json

For example, there is a user:

ID: 1

Name: James

Telphone: 1234

Email: 123@123.com

Fax: 5678

The database is saved as follows:

[{"ID": 1, "Name": "Zhang San", "Telphone": "1234", "Email": "123@123.com", "Fax ": "5678"}]

When I heard of this design idea, the tiger body was slightly shocked: depend, this is all right. According to this design, all my tables are saved in a json file. But think about it after being shocked. In fact, this design is also desirable.

First of all, in terms of queries, like ordinary youth, only one SQL statement is required:

Select Contact from table where Condition

After the query, you can use the json processing function to retrieve the desired data.

 

What will happen when the demand changes:

When adding a type-1 user, you need to add a table. It is also in line with the open and closed principle and the original code has not been changed

Add a contact method to store more things only when saving json.

 

However, this design requires a little effort to Update a piece of data: query a piece of data first, reorganize the json, and then Update

 

 

I have written so much that I hope I can clearly express the problems I want to express. Hope haihan is missing, and you are welcome to leave a message

The third method is novel and creative.