Restructuring the data room charging system-database design and data room Charging System

Restructuring the data room charging system-database design and data room Charging System

Finally, we reached the stage of restructuring the data center charging system ......

I used the database of the charging system of the data center for the previous time. I just read every table and didn't learn the database principle at the time. Then I didn't go into details ......

Now it is different. We have restructured the data center charging system. We have learned the database principles and gained a deeper understanding of the database. So we need to design the database well and follow the steps ......

 

Database technology is the most effective means of information resource management. Database Design refers to constructing the optimal database mode for a given application environment, establishing a database and its application system, and effectively storing data to meet user information requirements and processing requirements.

The steps and stages of database design are as follows:

Pay attention to the logic design phase

To convert a E-R diagram to a relational model is to convert the relationship between the object, the object attributes, and the object into a relational model. This conversion generally follows the following principles:

(1) A real shape is converted into a relational model. An object attribute is a link attribute. The entity code is the link code.

(2) A m: n link is converted into a link mode. The codes of the entities connected to the contact and the attributes of the contact are converted to the Link Attributes. The link code is a combination of Entity Codes.

(3) A 1: n link can be converted into an independent link mode, or merged with the link mode corresponding to n. If it is converted to an independent link mode, the codes of the entities connected to the contact and the attributes of the contact are converted to the link attributes, the link code is the entity code of n.

(4) A link can be converted into an independent link mode, or merged with the link mode corresponding to any end.

(5) One multivariate link between three or more entities is converted into a relational model. The codes and attributes of each object connected to the multivariate link are converted to the link attributes. The link code is a combination of Entity Codes.

(6) links between entities in the same entity set, I .e., self-links, can also be handled in three cases:, 1: n, and m: n.

(7) link modes with the same code can be merged.

(8) there is also the three paradigm that we often call to determine data dependencies. Eliminate redundant connections ):

1NF: each atom in relational mode R is an inseparable atomic value.

2NF: the relational mode R is 1NF, and each non-primary attribute is completely dependent on the candidate key (fields that can be used as primary keys). This satisfies the second paradigm.

3NF: the relational mode R is 1NF, and each non-primary attribute does not pass a candidate key dependent on R.

 

First, I re-designed it based on the original database to separate the original bloated tables, and to reduce the number of items ...... I drew an erimage:

Each table in the database is designed based on the erdiagram:

 

User information table (User_Info ):

 

UsrID	User Name (primary key)	Char (10)
Password	Password	Char (10)
Level	Level	Char (10)
UserName	Real name	Char (10)
Holder	Account Holder	Char (10)

 

Student Information table (Student_Info ):

StudentID	Student ID (primary key)	Char (10)
StudentName	Name	Char (10)
Sex	Gender	Char (2)
Department	Department	Char (10)
Grade	Grade	Char (10)
Class	Class	Char (10)

 

Card info table (card_Info ):

CardID	Card number (primary key)	Char (10)
StudentID	Student ID	Char (10)
Status	Usage Status	Bit
Account	Balance	Decimal (10, 4)
Type	Card Type	Char (10)
RegistDate	Registration Date	Date
RegistTime	Registration Time	Time
Checkstatus	Checkout status	Bit
UserID	User Name	Char (10)

 

Because students and cards are two different entities, their information is recorded separately to prevent data redundancy and table bloat.

 

Recharge ):

CardID	Card No.	Char (10)
RechargeMoney	Recharge Amount	Decimal (10, 4)
RechargeDate	Recharge date	Date
RechargeTime	Recharge time	Time
UserID	User Name	Char (10)
Checkstatus	Checkout status	Bit

 

Cancelcard ):

CardID	Card No.	Char (10)
ReturnMoney	Refund Amount	Decimal (10, 4)
CancelcardDate	Date of return	Date
CancelcardTime	Return time	Time
UserID	User Name	Char (10)
Checkstatus	Checkout status	Bit

 

OnOffLineRecord ):

CardID	Card No.	Char (10)
OnDate	Host date	Date
Ontime	Host time	Time
OffDate	Downtime	Date
Offtime	Downtime	Time
OffWay	Drop-down mode	Char (10)
ConsumeMoney	Consumption amount	Decimal (10, 4)
ConsumeTime	Consumption time	Time
UserID	User Name	Char (10)
Checkstatus	Checkout status	Bit
Computer	Machine name	Char (10)

 

Basic data table (BasicData ):

Leasttime	At least Machine Time	Time
Unittime	Unit increment time	Time
Rate	Fixed hourly fee	Decimal (10, 4)
Tmprate	Temporary hourly fee	Decimal (10, 4)
Limitcash	Minimum Amount	Decimal (10, 4)
Date	Date	Date
Time	Time	Time
UserID	User Name	Char (10)

 

Bill (check ):

LastcardMoney	Amount of recharge card in the previous period	Decimal (10, 4)
CurrentrechargeMoney	Current recharge card amount	Decimal (10, 4)
CurrentcancelcardMoney	Amount of cards withdrawn in current period	Decimal (10, 4)
CurrentconsumeMoney	Current consumption amount	Decimal (10, 4)
CurrentcardMoney	Current recharge card amount	Decimal (10, 4)
Date	Date	Date
Time	Time	Time
UserId	User Name	Char (10)

 

WorkLog ):

UserID	User Name	Char (10)
Ondate	Logon date	Date
Ontime	Logon Time	Time
Offdate	Cancellation date	Date
Offtime	Cancellation time	Time
Status	Status	Bit
Computer	Machine name	Char (10)

 

Summary: Database design is a very important thing, but it is impossible for us to perfect our database design at one time and follow the rules every time, only when there are more practices can we design a good database.