[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