Oracle Notes (16) database design paradigm

Oracle Notes (16) database design paradigm

The database design paradigm is a very important concept, but this important degree is only suitable for reference. Using the database design paradigm, you can make the data table better to save the data, because the reasonable design, if the data volume of a large there will certainly be a performance problem. So in the development, the only can be called the design of the treasure-design when possible to avoid the future of the application of multi-table association query.

I. First paradigm

The so-called first paradigm refers to the fact that data columns in a data table are not re-divided.

For example, there is now a data table like this:

CREATE TABLE Member (

Mid number PRIMARY KEY,

Name VARCHAR2 ($) is not NULL,

Contact VARCHAR2 ($)

);

This time the design is unreasonable, because the contact is composed of a variety of data: telephone, address, email, mobile phone, zip code, so this design is not consistent, can now modify the design:

CREATE TABLE Member (

Mid number PRIMARY KEY,

Name VARCHAR2 ($) is not NULL,

Address VARCHAR2 (+),

ZipCode VARCHAR2 (6),

Mobile VARCHAR2 (+),

Tel VARCHAR2 (+)

);

But here are two points to note:

• 1th, on the name, in foreign table design, the name is also divided into two categories of surname and name, but in China is the name of the preservation;
• 2nd, birthday, Birthday has a special data type (DATE), so it can not be set as a birthday year, birth and moon, birthday day;

The so-called indivisible refers to all data types that use the database to provide a good variety of data types.

Ii. the second paradigm: many-to-many

Second paradigm: a non-critical field in a data table has a partial function dependency on any of the candidate key fields;

The second paradigm is divided into two ways of understanding:

• Understanding One: There should not be a function relationship between columns, and now there is a design like this:

CREATE TABLE Orders (

OID number PRIMARY KEY,

Amount number,

Price number,

Allprice number

);

Total price of goods (allprice) = Commodity Unit Prices (price) * Number of goods (amount), so there is a function dependency;

• Understanding two: through a data table design embodies, complete a student elective system, if said now according to the first paradigm, then the following:

CREATE TABLE Studentcourse (

Stuid number PRIMARY KEY,

Stuname VARCHAR2 () not NULL,

CNAME VARCHAR2 () not NULL,

Credit number is not NULL,

Score Number

);

INSERT into Studentcourse (stuid,stuname,cname,credit,score) VALUES (1, ' Zhang San ', ' Java ',3,*) ;

INSERT into Studentcourse (stuid,stuname,cname,credit,score) VALUES (2, ' John Doe ', ' Java ',3,in.) ;

INSERT into Studentcourse (stuid,stuname,cname,credit,score) VALUES (3, ' Harry ', ' Java ',3,+) ;

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, ' John Doe ', ' 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 error:

• Data duplication: Students and curriculum data are in a repeating state, and the most serious problem is the setting of the primary key;
• Too many data updates: If you now have 3000 participants in a course, you will need to revise 3,000 records when you change a course credit, which is sure to have a performance impact;
• If a course is not attended by a student, the course is completely gone from the school;

If you want to work around this problem, you can modify the design of the data table as follows:

CREATE TABLE Student (

Stuid number PRIMARY KEY,

Stuname VARCHAR2 () not NULL

);

CREATE TABLE Course (

CID number PRIMARY KEY,

CNAME VARCHAR2 () not NULL,

Credit number is 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, ' John Doe ');

INSERT into student (stuid,stuname) VALUES (3, ' Harry ');

INSERT into Course (cid,cname,credit) VALUES (, ' Java ',3);

INSERT into Course (cid,cname,credit) VALUES (One, ' Oracle ',1);

INSERT into Course (cid,cname,credit) VALUES (N, ' Linux ',2);

INSERT into Studentcourse (stuid,cid,score) VALUES (1,.);

INSERT into Studentcourse (stuid,cid,score) VALUES (2,max);

INSERT into Studentcourse (stuid,cid,score) VALUES (3,ten,+);

INSERT into Studentcourse (stuid,cid,score) VALUES (1,One,each);

INSERT into Studentcourse (stuid,cid,score) VALUES (2,One,each);

This design is the same as the design of the sports meeting-project-achievement.

Iii. Third paradigm: one-to-many

For example, now there are multiple students in a school, if the first paradigm is not possible, and if the second paradigm is a many-to-many relationship, that is: a school has multiple students, a student in multiple schools, does not conform to the requirements, so at this time can use the third paradigm, referring to the previous department and employee operations to achieve, A department has multiple employees, so the design is as follows:

CREATE TABLE School (

Sid number PRIMARY KEY,

Sname VARCHAR2 () not NULL

);

CREATE TABLE Student (

Stuid number PRIMARY KEY,

Stuname VARCHAR2 () not NULL,

Sid Number REFERENCES School (SID)

);

In the actual work, the use of the third paradigm is the most.

The above three paradigms are used only as a reference.

Oracle Notes (16) database design paradigm