17, Three paradigms of database design
1. What is the database design paradigm?
The design basis of database table teaches you how to design database table.

2. What are the common database design paradigms?
Three.
The first paradigm: it is required that any table must have a primary key, and no field is atomic and can no longer be divided.
The second paradigm: Based on the first paradigm, it requires that all non primary key fields completely depend on the primary key. Do not create partial dependencies.
The third paradigm: Based on the second paradigm, all non primary key fields are required to directly rely on the primary key without transitive dependency.
When designing database tables, follow the above paradigm to avoid the honor of data and waste of space in the tables.

3. First paradigm
The most core and important paradigm, the design of all tables needs to be met.
There must be a primary key, and each field is atomic and cannot be divided.

| Student number | Student name | contact information               |
|    1001  |    Zhang San  | za@gmail.com,135000000 |
|    1002  |    Li Si  | ls@gmail.com,136000000 |
|    1003  |    Wang Wu  | ww@gmail.com,137000000 |
Do the above student lists meet the first paradigm?
	Not satisfied. First, there is no primary key; Second: contact information can be divided into email address and telephone number.
| Student number(pk) | Student name | e-mail address      |  contact number  |
|    1001      |    Zhang San  | za@gmail.com  |  135000000 |
|    1002      |    Li Si  | ls@gmail.com  |  136000000 |
|    1003      |    Wang Wu  | ww@gmail.com  |  137000000 |

4. The second paradigm
Based on the first paradigm, all non primary key fields must be completely dependent on the primary key without partial dependence.
This table describes the relationship between students and teachers.
|Student No. | student name | teacher No. | teacher name|
|1001 | Zhang San | 001 | Mr. Wang|
|1002 | Li Si | 002 | Miss Zhao|
|1003 | Wang Wu | 002 | Mr. Wang|
|1001 | Zhang San | 001 | Mr. Wang|

Analyze whether the above tables meet the first paradigm?
	dissatisfaction.

How to satisfy the first paradigm? modify
|   Student number +  Teacher number(pk)  |  Student name | Teacher name |
|    1001          001        |   Zhang San    | Miss Wang   |
|    1002          002        |   Li Si    | Miss Zhao   |
|    1003          002        |   Wang Wu    | Miss Wang   |
|    1001          001        |   Zhang San    | Miss Wang   |
Student No. and teacher No. these two fields are combined as primary key and compound primary key(pk:Student number + Teacher number)
After modification, the above table meets the first paradigm, but does not meet the second paradigm:
	"Zhang San's dependence on 1001 and Mr. Wang's dependence on 001 obviously produce partial dependence.

What are the disadvantages of partial dependency?
	Data redundancy and space waste. "Zhang San" and "Mr. Wang" repeated.

In order to make the above table meet the third paradigm, you need to design it as follows:
	Use three tables to represent many to many relationships!!!
		Student list
		| Student number(pk) | Student name | 
	    |    1001     |    Zhang San  | 
	    |    1002     |    Li Si  | 
     	|    1003     |    Wang Wu  | 
     	Teacher table
     	| Teacher number(pk) |  Teacher name |
	    |        001   |   Miss Wang  |
	    |        002   |   Miss Zhao  |
		 Student teacher relationship form
		 |id(pk) | Student number(fk) | Teacher number(fk) | 
         |    1  |     1001     | 	 001       |
   		 |    2  |     1002     |	 002       |
 		 |    3  |     1003     | 	 002       | 
 	     |    4  |     1001     | 	 001       | 

Recite the formula:
	How to design many to many?
		Many to many, three tables, relation table, two foreign keys!!!!!

5. The third paradigm
Based on the second paradigm, it requires that all non primary key dictionaries must directly rely on the primary key without transitive dependency.
|Student number (pk) | student name | class number | class name|
|1001 | Zhang San | 01 | one class a year|
|1002 | Li Si | 02 | class 2 a year|
|1003 | Wang 5 | 03 | class 3 a year|
|1004 | Zhao Liu | 03 | class 3 a year|
The design of the above table is to describe: the relationship between class and students is obviously a 1-to-many relationship!
There are multiple students in a teacher.

    Analyze whether the above table meets the first paradigm?
    	It satisfies the first normal form and has a primary key.
    
    Analyze whether the above table meets the second paradigm?
    	The second paradigm is satisfied because the primary key is not a composite primary key and does not generate partial dependencies. The primary key is a single primary key.
    
    Analyze whether the above table meets the third paradigm?
    	The third paradigm requires: do not generate partial dependence!
    	One shift a year depends on 01, and 01 depends on 1001, resulting in transmission dependency.
    	It does not meet the requirements of the third paradigm, resulting in data redundancy.

	So how to design one to many?
		Class schedule: 1
			 | Class number(pk) |  Class name |
 			 | 		 01 	|  Once a year |
 			 |  	 02 	|  Two classes a year |
 		     | 		 03 	|  Three classes a year |
 		Student list: multiple
 			| Student number(pk)  | Student name | Class number(fk) |
		    |    	1001    |    Zhang San  | 	01		  | 
		    |    	1002    |    Li Si  |	02	      | 
	     	|  	    1003    |    Wang Wu  | 	03		  | 
	        |  	    1004    |    Zhao Liu  | 	03 		  | 

		Recite the formula:
			One to many, two tables, many tables plus foreign keys!!!!

6. Design of summary table

One to many:
	One to many, two tables, many tables plus foreign keys
	
Many to many:
	Many to many, three tables, relational tables, two foreign keys

one-on-one:
	Don't you just put one-on-one in one table? Why split a table?
	In the actual development, there may be too many and too large fields in a table. At this time, it is necessary to split the table.
	How to design one-on-one?
		Before splitting a table: a table
		t_user
		id       login_name    login_pwd     real_name       email                 address....
		1        zhangsan        123                Zhang San              zhangsan@xxx
		2        lisi                    123               Li Si               lisi@xxx
		...
		This huge table is recommended to be split into two tables:
		t_login Login information table
		id(pk)        login_name         login_pwd
		1               zhangsan            123
		2               lisi                        123
		t_user User details table
		id(pk)         real_name         email                   address.....       login_id(fk+unique)
		100            Zhang San                   zhangsan@xxx
		200            Li Si                    isi@xxx
		
		Pithy formula: one-to-one, unique foreign key!!!!!

7. Attention!!!!!!

The three paradigms of database design are theoretical.
Time and theory sometimes deviate.
The ultimate goal is to meet the needs of customers. Sometimes redundancy will be exchanged for execution speed.
Because in sql Among them, the more connections between tables, the lower the efficiency(Cartesian product). 
Sometimes redundancy may exist, but it is also reasonable to reduce the number of table connections.
And for developers, sql The difficulty of writing statements will also be reduced.

Add this to the interview*****