For more information about SQL Server database design

When I have any questions, I often come back to the blog Park to find the answer. Over time, I have summarized some things.
Please give your comments and comments on your mistakes.

Three basic principles of database design paradigm
Paradigm 1: fields in a database table are single attributes and cannot be further divided. This single attribute is composed of basic types, including integer, real number, complex type, logical type, and date type.
That is to say, do not see the following situations

Student Information

One shift per year, 97001, Zhang San

This is easy to do, huh, huh.

Second paradigm: some function dependencies between non-Keyword fields and any candidate keyword fields in the database table (some function dependencies refer to the condition where some fields in the composite keyword determine non-Keyword fields ), that is, all non-Keyword fields depend entirely on any set of candidate keywords.
That is to say, do not see the following situations

Student ID	Name	Age	Course name	Score	Credits
97001	Zhang San	13	Chemistry	88	2

The student ID and Course name are the joint primary keys.

Because:
(Course name) → (credits)
(Student ID) → (name, age)

Third paradigm: Based on the second paradigm, if the data table does not have a transfer function dependency of a non-Keyword segment on any candidate keyword segment, it complies with the third paradigm. The so-called pass function dependency refers to the existence of "A → B → C" decision relationship, then the C transfer function depends on. Therefore, database tables that meet the third paradigm should not have the following dependency:
Keyword field → non-Keyword field X → non-Keyword field y

That is to say, do not see the following situations

Student ID	Name	Age	School	School location	School phone number
97001	Zhang San	13	Tsinghua	Zhongguancun	8888888

Because: (student ID) → (school location, school phone number)
Note: The third paradigm can be broken sometimes to improve efficiency! It is usually seen in tables with a large volume of foreign keys and data. To improve query efficiency, you can sacrifice the efficiency of addition, deletion, and modification.

About tables, views, and stored procedures:
A table is used to store data. It must satisfy three paradigms as much as possible and avoid redundancy.
A view is used to query data. Basic Tables without foreign keys can be directly used to query data. For business tables with many foreign keys, query operations must all be performed through the view.
I basically don't need stored procedures and triggers. I tend not to reflect too many businesses (or even not) at the database level. I focus all my businesses onCodeLevel. In fact, there is another reason why I am not very proficient in this technology. Sorry.

About indexes:
A friend once gave a good example. An aggregate index is like a pinyin search, and a non-aggregate index is like a partial index.
Pinyin indexes are sorted in the entire dictionary, just like querying English words. You only need to follow the English indexes on each page to flip back or forward to find the words you want. However, it would be terrible to create a new word and insert it into the dictionary. All the words behind the new word should be moved backwards, it means that the dictionary is re-executed. Therefore, aggregate indexes are convenient for searching, sorting, and filtering (for example, I want to see all the words from A to C.
However, even if there is an aggregate index, some SQL keywords still need to cause full table scanning. For example, if I want to find a word similar to * Ng (like '% ng '), you need to search for the entire dictionary (full table scan), but it is easy to find words like 'ac %.

A non-aggregate index is a one-to-one relationship (a non-aggregate index is the address corresponding to each content). You can find a record and locate it. You can think about what types of fields can be used as non-aggregate indexes. Yes, one field is similar to the document number. Each record is different, and only one field is found almost every time. What else? Yes, foreign keys and Foreign keys all need non-aggregate indexes. I have tested that a table has many foreign keys and the views are connected by inner join. In contrast, if no non-aggregated foreign key index is set up for 0.1 million data records, try to enable it for 12 seconds and create a non-aggregated index for each foreign key. The speed of enabling the index is shortened to 6 seconds.
I will refer to the original instructions in the online document to describe:
Before creating a non-clustered index, you should first learn how to access data. Consider using non-clustered indexes for queries with the following attributes:

• Use the join or group by clause.
  Multiple non-clustered indexes should be created for the columns involved in join and group operations, and one clustered index should be created for any foreign key column.
• No query of large result sets is returned.
• Contains columns that are frequently included in the search criteria (for example, return a fully matched WHERE clause.

About Primary keys:
I prefer the Business independence of the primary key, using the famous guid. Although it occupies a large amount of space and has low efficiency, no other better methods can be found.
Note that when creating a primary key, SQL Server sets the primary key as an aggregate index by default. You must remove the primary key and set it to other meaningful fields, or do not set it at all.

Guid has many advantages:
It is easy and predictable to generate a primary key.
There is no worries about repeated primary keys during concurrency.
This prevents you from Manually changing the data in the database. When you see the guid, you are all scared back.
Avoid the trouble of migrating database tables (using auto-incrementing primary keys is a disaster during table migration ).
This avoids the cascade update of Foreign keys when the basic table is updated (mainly reflected in the Business independence of primary keys ).

You are welcome to give more comments.