The database design paradigm is a very important concept, but this importance is only suitable for reference. With the database design paradigm, data tables can be better stored, because a reasonable design will certainly have performance problems if the data size is large. Therefore, during development, the only template that can be called design-avoid futureProgramMulti-table join query appears.
I. First paradigm
The so-called first paradigm means that data columns in a data table cannot be further divided.
For example, there is a data table as follows:
Create TableMember (MidNumber Primary Key, NameVarchar2(200)Not Null, ContactVarchar2(200));
The design at this time is unreasonable, because the contact information is composed of a variety of data: telephone, address, email, mobile phone, zip code, so this design is not consistent, now you can modify the design:
Create Table Member (Mid Number Primary Key , Name Varchar2 ( 200 ) Not Null , Address Varchar2 ( 200 ), Zipcode Varchar2 ( 6 ), Mobile Varchar2 ( 20 ), Tel Varchar2 ( 20 ));
However, there are two points to note:
- First, for names, in foreign table design, names are also divided into two types: surname and name, but in China, names are saved;
- Second, there is a special data type (date) for birthdays and birthdays, so it cannot be set to the year of birth, the month of birth, or the day of birth;
The so-called inseparable means that all data types use the various data types provided by the database.
Ii. Second paradigm: many-to-many
Second paradigm:Non-Keyword fields in the data table have some function dependencies on any candidate key fields;
The second paradigm can be understood in two ways:
- Understanding 1:There should be no functional relationship between columns, as shown in the following design:
Create TableOrders (OIDNumber Primary Key, AmountNumber, PriceNumber, AllpriceNumber);
Currently, the total price of a product (allprice) = the unit price of a product (price) * The number of items (amount), so there is a function dependency;
- Understanding 2:The design of a data table shows how to complete a course selection system for students. If the system follows the first paradigm, it is as follows:
Create Table Studentcourse (stuid Number Primary Key , Stuname Varchar2 ( 20 ) Not Null , Cname Varchar2 ( 50 ) Not Null , Credit Number Not Null , Score Number ); Insert Into Studentcourse (stuid, stuname, cname, credit, score) Values ( 1 , 'Zhang san', 'java ', 3 , 89 ); Insert Into Studentcourse (stuid, stuname, cname, credit, score) Values ( 2 , 'Lily', 'java ', 3 , 99 ); Insert Into Studentcourse (stuid, stuname, cname, credit, score) Values ( 3 , 'Wang 5', 'java ', 3 , 78 ); Insert Into Studentcourse (stuid, stuname, cname, credit, score) Values ( 1 , 'Zhang san', 'oracle ', 1 , 79 ); Insert Into Studentcourse (stuid, stuname, cname, credit, score) Values ( 2 , 'Li si', 'oracle ',1 , 89 );
This design conforms to the first design paradigm, but does not conform to the second paradigm, because the program has the following errors:
- Data duplication: The data of both students and courses is in the duplicate state, and the most serious problem is the primary key setting problem;
- Too many data updates: if there are already 3000 participants in a course, 3000 records need to be modified when you change the course credits, which will definitely affect the performance;
- If no student takes part in a course, the course disappears from the school;
To solve this problem, you can modify the design of the data table as follows:
Create Table Student (stuid Number Primary Key , Stuname Varchar2 (20 ) Not Null ); Create Table Course (CID Number Primary Key , Cname Varchar2 ( 50 ) Not Null , Credit Number Not Null ); Create Table Studentcourse (stuid Number References Student (stuid), CID Number References Course (CID), score Number ); Insert Into Student (stuid, stuname) Values ( 1 , 'Zhang san '); Insert Into Student (stuid, stuname) Values ( 2 , 'Li si '); Insert Into Student (stuid, stuname) Values ( 3 , 'Wang wu '); Insert Into Course (CID, cname, credit) Values ( 10 , 'Java ', 3 ); Insert Into Course (CID, cname, credit) Values ( 11 , 'Oracle ', 1 ); Insert Into Course (CID, cname, credit) Values ( 12 , 'Linux ', 2 ); Insert Into Studentcourse (stuid, CID, score) Values ( 1 , 10 , 89 ); Insert Into Studentcourse (stuid, CID, score) Values ( 2 , 10 , 99 ); Insert Into Studentcourse (stuid, CID, score) Values ( 3 , 10 ,78 ); Insert Into Studentcourse (stuid, CID, score) Values ( 1 , 11 , 79 ); Insert Into Studentcourse (stuid, CID, score) Values ( 2 ,11 , 89 );
This design is the same as the previous design of sports games-projects-achievements.
3. Third paradigm: one-to-many
For example, if a school has multiple students and the first paradigm cannot be used, but the second paradigm represents the many-to-many relationship, that is, a school has multiple students, A student is in multiple schools and does not meet the requirements. Therefore, the third paradigm can be used. refer to the previous Department and employee operations. A Department has multiple employees. Therefore, the design is as follows:
Create Table School (SID Number Primary Key , Sname Varchar2 (20 ) Not Null ); Create Table Student (stuid Number Primary Key , Stuname Varchar2 ( 20 ) Not Null , Sid Number References School (SID ));
In actual work, the third paradigm is the most used.
The above three paradigms are for reference only.