Oracle note (16) database design paradigm

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.