Database Design of Data room Charging System

Previously, when I was studying and writing documents about the data center charging system, I wrote this article about the concept design model ERTU of the data center Charging System database. Now I am in the reconstruction phase of the individual version of the data center charging system, we need to design the database again. It can be said that the concept design of the previous database has laid a foundation for my current design, but I still find that my design involves

Previously, when I was studying and writing documents about the data center charging system, I wrote this article about the concept design model ERTU of the data center Charging System database. Now I am in the reconstruction phase of the individual version of the data center charging system, we need to design the database again. It can be said that the concept design of the previous database has laid a foundation for my current design, but I still find that my design involves

Previously, when I was studying and writing documents about the data center charging system, I wrote the concept design model of the data center Charging System database-ERTU. Now I am in the reconstruction phase of the individual version of the data center charging system, we need to design the database again. It can be said that the concept design of the previous database has laid a foundation for my current design, but I still find that there are many unreasonable aspects in my design that need improvement.

In this database design, I learned some database naming rules and reviewed the classic three Paradigm (attribute atomicity, avoiding local dependence and avoiding dependency transmission ). However, we found that in the face of requirements, some fields belonging to two tables must be put in one table for convenience and have to break the three paradigm.

Now I will share my own database design as follows: (because I have not actually reconstructed the data center, I do not know whether these tables are reasonable in actual application. Please give us your valuable comments .)

Database Name: ComputerRoomChargeSystem

Student Information table (T_StudentInfo)

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

Here, I divide the student information and card information into two tables. First, considering that they belong to different entities, and secondly, I think that if the card is no longer used, you have to delete the card information, so the student information has to be deleted (however, later I thought that the card information does not need to be deleted, it can be distinguished by marking its status as "not used ). Finally, I feel like putting so many fields in a table seems too bloated.

User information table (T_UserInfo)

Name	Meaning	Type
UserID	User Name (primary key)	Char (10)
RealName	Real name	Char (10)
UserLevel	User level	Char (8)
UserPassword	User Password	Char (10)
AccountHolder	Account Holder	Char (10)

Card information (T_CardInfo)

Name	Meaning	Type
CardID	Card number (primary key)	Char (10)
StudentID	Student ID (foreign key)	Char (10)
Account	Balance	Decimal (10, 4)
UsageState	Usage Status	Char (6)
CardType	Card Type	Char (8)
RegistrationDate	Registration Date	Date
RegistrationTime	Registration Time	Time (0)
UserID	User Name	Char (10)
CheckStatus	Checkout status	Bit (1)

Here, the status indicates whether the card is used or not (the card has been withdrawn ). Card type to mark as a fixed user or a temporary user.

Bill (T_AccountSheet)

Name	Meaning	Type
CheckID	Checkout number (primary key)	Decimal (18,0)
LastCardMoney	Amount of recharge card in the previous period	Decimal (18,4)
CurrentChargeMoney	Current Recharge Amount	Decimal (18,4)
CurrentReturnMoney	Amount of cards withdrawn in current period	Decimal (18,4)
CurrentConsumeMoney	Current consumption amount	Decimal (18,4)
CurrentCardMoney	Current recharge card amount	Decimal (18,4)
CheckDate	Checkout date	Date
CheckTime	Checkout time	Time (0)
UserID	User Name	Char (10)

Bill, I have set a bill number for it as the primary key. I want to sort the bill number from large to small when creating a table, because the bill is printed or settled on a daily basis, weekly checkout is all about closing the latest bill.

Current recharge card amount = recharge card amount in the previous period + current Recharge Amount-current consumption amount-current refund amount

Recharge record table (T_ChargeRecord)

Name	Meaning	Type
CardID	Card No. (foreign key)	Char (10)
ChargeDate	Recharge date	Date
ChargeTime	Recharge time	Time (0)
ChargeMoney	Recharge Amount	Decimal (10, 4)
CheckStatus	Checkout status	Bit (1)
UserID	User Name	Char (10)

Card return record table (T_ReturnRecord)

Name	Meaning	Type
CardID	Card No. (foreign key)	Char (10)
ReturnDate	Date of return	Date
ReturnTime	Return time	Time (0)
Account	Refund Amount	Decimal (10, 4)
CheckStatus	Checkout status	Bit (1)
UserID	User Name	Char (10)

Online/offline record table (T_OnOffLineRecord)

Name	Meaning	Type
CardID	Card No. (foreign key)	Char (10)
OnDate	Host date	Date
OnTime	Host time	Time (0)
OffDate	Downtime	Date
OffTime	Downtime	Time (0)
OffWay	Drop-down mode	Char (8)
ConsumeMoney	Consumption amount	Decimal (10, 4)
UserID	User Name	Char (10)
CheckStatus	Checkout status	Char (6)
OnFlag	On-board flag	Bit (1)

In this table, I added the check Status field, because when I thought of the check, in addition to the number of cards, the amount of recharge, the amount of cards to be paid off, and the amount to be consumed.

Basic data table (T_BasicData)

Name	Meaning	Type
FixedPerCharge	Fixed hourly fee	Decimal (10, 4)
TemporaryPerCharge	Temporary hourly fee	Decimal (10, 4)
IncreasingUnitTime	Increment unit time	SmallInt
LeastTime	At least Machine Time	SmallInt
PrepareTime	Preparation Time	SmallInt
MinMoney	Minimum Amount	Decimal (10, 4)

Operator work record (T_WorkLog)

Name	Meaning	Type
UserID	User Name (foreign key)	Char (10)
LoginDate	Logon date	Date
LoginTime	Logon Time	Time (0)
ExitDate	Cancellation date	Date
ExitTime	Cancellation time	Time (0)
OnFlag	On-board flag	Bit (1)
ComputerID	Machine name	Varchar (10)

Summary: I feel that database design is a very interesting thing. When drawing the class diagram of the data center reconstruction version, I feel that we have to start with the database design first. Therefore, database design is very important!