Shulou(Shulou.com)06/01 Report--

In a recent project, I encountered a data model of a base station, which has seven attributes, several attributes of the second layer under each attribute, and several attributes of the third layer under the second layer, up to more than 1000 of the lowest attributes of the fifth layer. now the base station should be designed into seven tables according to seven attributes, and the lowest attributes of each attribute range from 100 to more than 400.

How to design it?

First, it is most common to build tables with the lowest attributes of each attribute, with the largest table having more than 400 fields, which is not good from performance to maintenance.

Second, according to the relationship between the fact table and the latitude table, each major attribute is divided into small attributes to design the table, the result may be dozens of tables, there is a complex logical relationship between each other, so it is not convenient in business processing.

Third, according to the object-oriented design method of Oracle, the third layer is used to design objects, that is, the third layer properties under each major attribute are designed as each object, and then the object is used to define the table.

The original data model is too complex to give an example. Let's use a simple example to illustrate.

For example, common employee tables have these fields (eid, ename, sex, borthady, address, phone, job, dept, manager, sal, subsidy, tax, school, grad, educa, degree, extcol, extcol2, extcol3,)

It is designed into the following classes according to the category of the field:

-- basic categories of employees

Create or replace type ty_person as object

(ename varchar2 (20),-name

Sex char (1)-gender

Borthady date-- birthday

Address varchar2 (20)-- address

Phone varchar2 (20)-telephone

);

-- basic classes of work

Create or replace type ty_job as object

(job varchar2 (20),-position

Dept varchar2 (20)-- Department

Manager varchar2 (20)-Manager

);

-- basic categories of wages

Create or replace type ty_sal as object

(sal number,-- salary

Subsidy number,-subsidy

Tax number-taxes and fees

);

-- basic categories of education

Create or replace type ty_edu as object

(school varchar2 (20),-graduation school

Grad date,-- graduation time

Educa varchar2 (20)-- academic qualifications

Degree varchar2 (20)-degree

);

-- use the above class to construct the employee table

Create table t_emp

(eid varchar2 (20) not null

Person ty_person

Job ty_job

Sal ty_sal

Edu ty_edu

Extcol varchar2 (50)

Extcol2 varchar2 (50)

Extcol3 varchar2 (50)

Extcol4 varchar2 (50)

Extcol5 varchar2 (50)

);

-- extcol represents an extended field

The basic operations of the class are described below:

1. Use the constructor to add data

Insert into t_emp (eid, person,job,sal,edu, extcol,extcol2,extcol3, extcol4,extcol5)

Values ('001'

Ty_person ('scott','M',to_date (' 1981-10-10 mm beijing','0102345678'))

Ty_job ('DBA','IT','smith')

Ty_sal (8000,000,500)

Ty_edu ('Peking University',to_date (' 2004-10-10 mm bachelor','bachelor'))

Null,null,null,null,null)

two。 Query data

Select * from t_emp t

-- check the data of a certain class

Select t.person.name,t.person.sex,t.person.borthady,t.person.address,t.person.phone

From t_emp t

3. Delete data

Delete t_emp t where t.person.nameboy

4. Modify data

Update t_emp t set t.person.nameboy

Where t.person.nameboy

UPDATE t_emp e

SET e.person = ty_person ('mark','M',to_date (' 1982-10-10mm neijing','0212345678'))

WHERE e.eid = '001'

These are the basic operations. For more object-oriented operations, please see the Oracle documentation.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.