©2020-2024 ioDraw All rights reserved
After a few days to Shaoguan a university database design training , The process of database design has a more profound understanding .
<> One , Steps of database design
（1） Understanding functional requirements
Before designing the database , Designers must first understand the functional requirements of the system . Here you can read the product requirements specification , Project related personnel （ For example, the project manager , Customers, etc ） Communicate fully .
（2） Defining entities
After understanding the functional requirements of the system , Designers define the entities of the system by analyzing the system functions . such as ： Open a room at the hotel . There should be at least two entities here ： Guests and rooms .
After defining the entity , We also define the properties of the entity . for example ：
guest ： full name , Gender , phone number , Identification Number , Document type （ ID , driver's license , Pass and so on ）…
room ： room number , Room type （ single room , Double room , Luxury rooms and so on ）, Check in time , Departure time , Room status （ Checked in , Not checked in ）…
（3） draw E-R chart
After defining entities , Next, we should draw them based on the entities and the relationships between them E-R chart . such as ：
A rectangle represents a solid , Ellipses represent the attributes of an entity , The diamond represents the relationship between entities .
（4） hold E-R Graph to model
Draw out E-R After the picture , We need to build physical models from it . There are several tools you can use to build a physical model , For example, it is quite popular at present PowerDesigner.
（5） Check the model
After model design , We also need to check whether the model meets the requirements of the third normal form . If not, the model needs to be revised again , Until the requirements of the third paradigm are met .
for instance , The above model does not meet the requirements of the third normal form . because customer and room Both tables have fields that are not directly related to the table . If we want to meet the requirements of the third paradigm , You need to change the model to ：
Three tables are added to the above model , namely identity_type（ Certificate type table ）,register（ registration form ）,room_type（ Room type table ）, After modifying the model , It has met the requirements of the third paradigm .
（5） Define database based on Model
Different databases SQL There may be a small difference in the order . For example, we use it here MySQL database .
Commands for defining databases ：DDL
# Create database create database Database name ; # Delete database drop database Database name ; # query data base show databases;
# Select database use Database name ; # Create table create table Table name ( Listing data type [primary key] [auto_increment],
Listing data type [not null] [unique] [default ' Default value '] [comment ' Field description '], Listing data type [not
null] [unique] [default ' Default value '] [comment ' Field description '], ... [constraint Foreign key name foreign
key( Foreign key column ) references Table name ( Primary key column ) [on update|delete cascade]] ); # Delete table drop table Table name ;
The database is defined according to the model ：
# Create database create database hotel; # query data base show databases; # Select database use hotel; # Certificate type table
create table identity_type ( identity_type_id int primary key auto_increment,
identity_type_name varchar(50) not null comment ' Name of certificate type ' ); # Guest list create table
customer ( cust_id int primary key auto_increment, cust_name varchar(255) not
null unique default '' comment ' Guest name ', mobile varchar(11) default '' comment
' phone number ', identity_num varchar(50) not null unique default '' comment ' Identification Number ',
identity_type_id int not null comment ' Foreign key column , Certificate reference table ', constraint
fk_cust_identity_type foreign key(identity_type_id) references
identity_type(identity_type_id) ); # Room type create table room_type ( room_type_id
int primary key auto_increment, room_type_name varchar(50) not null comment
' Room type name ' ); # room create table room ( room_id int primary key auto_increment,
room_num varchar(10) not null comment ' room number ', room_type_id int not null comment
' Foreign key column , Reference room type table ', status int not null comment ' Room status ,1 Representative not in ,2 Representative has checked in ', constraint
fk_room_type foreign key(room_type_id) references room_type(room_type_id) );
# registration form create table register ( cust_id int not null comment ' Foreign key , Quote guest list ', room_id
int not null comment ' Foreign key , Reference room table ', in_time datetime not null comment ' Check in time ',
out_time datetime comment ' Departure time ', constraint fk_register_cust foreign
key(cust_id) references customer(cust_id), constraint fk_register_room foreign
key(room_id) references room(room_id) )
thus , The task of database design phase has been completed .
<> Two , summary
Database design is not difficult , I think the difficulty lies in the initial analysis stage . It is how to put the entities in the database according to the needs of customers , And the relationship between entities . therefore , In the database design phase , We should focus on the analysis of business requirements , Accurately grasp the needs of customers , Only in this way can we design a set of better database .
Technology