"DAO" Billing system database design Simple Document

design documentation for the database

The design document for the database, the SQL statement that needs to build the table.

1) account accounts

ID Number (9) PK account ID

login_name varchar2() notnull user Self service user name

login_passwd VARCHAR2 (8) NOT null user self service password

status char (1) NOT NULL 0: open 1: pause frozen 2: Delete real data rarely deleted

create_date date not NULL account open

pause_date Date account was frozen

close_date Date accounts stop deleting dates

real_name varchar2 () NOT NULL customer real name

Idcard char2 (+) NOT null ID

birthdate date not null birthday

gender char (1) NOT null sex f: female m: male

Occupation Varchar2 (a) occupation

telephone varchar2 () not nulll telephone

Email varchar2 (email)

mailaddress varchar2 (+) NOT NULL communication address

ZipCode char (6) NOT NULL postal code

QQ VHARCHAR2 () QQ No.

2) service Business type basic information and corresponding tariff

ID Number (TEN) PK

account_id Number (9) NOT null FK associated account

cost_id Number (4) NOT NULL FK Association Cost

Host VARCHAR2 (not null) IP Address

os_username VARCHAR2 (8) NOT NULL login host user name

os_passwd VARCHAR2 (8) NOT NULL login host Password

Status char (1) NOT NULL business account State 0: Open 1: Pause 2: Delete (service status and account status is related)

create_date date NOT null Business Account Opening date

pause_date Date business account is frozen

close_date Date business account stop deleting dates

3) cost Tariff table

ID Number (4) PK

Name VARCHAR2 (a) NOT NULL tariff names

base_duration Number (one) packet length (hours): Package for several months , how many hours. For the future of the expansion of the package , the general number is taken one,Java maximum length is ten bits. Interger.max_value

base_cost Number (7,2) Kiffe (yuan): the corresponding monthly basic billing, including the basic costs.

unit_cost Number (7,2) unit fee (yuan / min) per hour fee, not monthly

descry varchar2 ($) NOT NULL tariff basic description

Cost_type char (1) NOT NULL tariff category 1: monthly subscription 2: Set 3: Timing

create_date date NOT NULL created

4) service_detail All users login on the server, log out the information

ID number (one) PK

service_id Number (TEN) FK NOT null associated service

client_host varchar2 () NOT null client IP

PID Number (one) NOT null process ID

login_time date NOT null login time

logout_time date not null exit time

duration Number (20,9) not null logon duration // not primary key sole decision, for statistical efficiency

Host VARCHAR2 (not null) IP Address

os_username VARCHAR2 (8) NOT NULL login host user name

os_passwd varchar (8) NOT NULL login host Password

Operations of the main business:

1. Create a new account and open a business account: Operation account,service table

2. existing Accounts account increase business account number: Operation service table, query accounts table

3. System Acquisition and logging information : Operation service_detail, query service table

4. Settlement of a billing account at the end of the month: Query the Accounts table, query service form Query business, query the record of service_details Login, operation cost table.

5. Add a tariff standard: operate the service table

"DAO" Billing system database design Simple Document