[DAO] simple document of billing system database design, dao Billing System Database

[DAO] simple document of billing system database design, dao Billing System Database
Database Design Documents

Database Design Documents, SQL statements for table creation.

1) account

Id number (9) PK account ID

Login_name varchar2 (20) not null user self-service Username

Login_passwd varchar2 (8) not null user self-service Password

Status char (1) not null 0: Activate 1: suspend frozen 2: delete real data rarely Delete

Create_date date not null Account Activation date

Pause_date date the account is frozen

Close_date date: date when the account is deleted

Real_name varchar2 (20) not null real customer name

Idcard char2 (18) not null ID card

Birthdate date not null birthday

Gender char (1) not null gender f: female m: Male

Occupation varchar2 (50) occupation

Telephone varchar2 (50) not nulll phone

Email varchar2 (50)

Mailaddress varchar2 (100) not null communication address

Zipcode char (6) not null zip code

Qq vharchar2 (15) qq No.

2) Basic service Information and corresponding charges

Id number (10) pk

Account_id number (9) not null FK associated account

Cost_id number (4) not null FK associated with cost

Host varchar2 (15) not null host IP Address

OS _username varchar2 (8) not null logon host Username

OS _passwd varchar2 (8) not null logon host Password

Status char (1) not null business account status 0: Activation 1: Paused 2: deleted (service status and account status are related)

Create_date date not null Business Account Activation date

Pause_date date the business account is frozen

Close_date date business account stop deletion date

3) cost fee table

Id number (4) PK

Name varchar2 (50) not null charge name

Base_duration number (11) package duration (hours): Package duration (months), package duration (hours. For future subscription expansion, generally the number is 11, and the maximum length of java is 10 characters. Interger. MAX_VALUE

Base_cost number () base fee (RMB): the basic fee of the corresponding subscription, including the basic fee.

Unit_cost number () unit fee (RMB/minute) per hour, not monthly

Descry varchar2 (200) not null basic description

Cost_type char (1) not null tariff category 1: monthly subscription 2: package 3: Timing

Create_date date not null creation date

4) service_detail logon and logout information of all service_detail users on the server

Id number (11) PK

Service_id number (10) FK not null associates with service

Client_host varchar2 (15) not null Client IP

Pid number (11) not null process ID

Login_time date not null Logon Time

Logout_time date not null Exit Time

Duration number (20, 9) not null logon duration // it is not the only decision of the primary key, for statistical efficiency

Host varchar2 (15) not null host IP Address

OS _username varchar2 (8) not null logon host Username

OS _passwd varchar (8) not null logon host Password

 

 

Main business operations:

1. Create an account and activate a business account: operate accounts and service tables.

2. Add a business account to an existing account: operate the service table and query the account table.

3. The system collects and records the log information: service_detail to query the service table.

4. Settle an account at the end of the month: query the account table, query the service Table query service, query the service_details logon records, and operate on the cost table.

5. Add a tariff standard: Operation service table