One section after another, thousands of branches save thousands of leaves. I don't blossom. I don't have to flirt with bees and butterflies.
Haoshou is still greedy for learning, modest and virtuous. When the hidden spirit has no me, I understand things without saying.
preface
I open source it. But I hope you can learn the idea of database design
Admittedly, I am not a DBA. I have only read half a high-performance MySQL (version 3), an introduction to database system (version 5) and a java development manual (Huashan version). I have mastered some knowledge and applied them to the course design. Maybe my database course design is not the best and best for the project structure
However, with the experience of these books, I think there are still some advantages. In this database system design, I always hope to ask myself to be an excellent DBA standard
Not for anything else. Just want to do it once, do it well!
Because I had the idea of open source before I did it, I fully considered the mysql table design idea, avoiding constraints, locking, triggers, stored procedures, etc
Therefore, this course design not only has the idea of logical operation, but also the shackles of triggers and stored procedures for the purpose of teaching
Therefore, I hope to do a comprehensive: consideration of storage engine, table design, comparison of adopted schemes, index optimization, database stress test under windows and delay response time test under linux
These aspects are included
Hope to learn and make progress together
You can join my qq group: 287048847 to exchange database design and Java/Go development together! People here are full of passion!
Tip: the following is the main content of this article. The following cases can be used for reference
Hospital information management system
abstract
With the continuous improvement of people's living standards, people pay more attention to medical facilities, and the business of the hospital is also increasing. For the hospital, the information management system is an important part. Ensuring the efficient, stable and safe operation of the system is a problem that the hospital should pay attention to and solve. In order to make the information system stable, safe and highly available, the staff should manage the information system well, Pay attention to database security, so that information data can give full play to its role and value. Medical treatment is related to people's livelihood and people's health. Designers should be extra cautious and do a good job of zero mistakes
1. General
According to the requirements of database course design, I will write and design this system with DBA standard, referring to MySQL high performance, java development manual, introduction to database system, etc. considering many factors, my understanding of database and my current experience level, I will use java to connect mysql database. shell script, jmeter testing tool for testing. The reason why this system uses MySQL is that it is more mature than sql server under Linux, and it has higher flexibility. Java is selected as the database connection, because I am familiar with it compared with other languages, and it is also powerful enough. shell and jmeter can well assist me in benchmark testing. According to the requirements of DBA, we should use more logical foreign keys, Less triggers, stored procedures and foreign key cascading operations. But I will give two schemes together. See the following for the specific design scheme
Operating environment
mysql-connector-java-8.0.22.jar
Window10
IntelliJ IDEA 2021.2.1
apache-jmeter-5.4.1
linux-centos7
spring bean connection pool
2.1 demand analysis
2.1.1 basic classification demand analysis
1. Control center
| Personnel management: | Realize the addition, deletion and modification of employees and patients; |
| drug management: | Realize the warehousing and outbound operation of drugs, the registration of prescription drugs, as well as the registration of drug types and drug information |
| charge management: | Statistics of drug revenue; |
2. Query
basic information query of employees and patients;
query of drug information;
inquiry of charging;
2.1.2 main relationship process analysis
When the patient sees a doctor, first register and wait for the Department to be assigned. Then the doctor issues a bill for the patient. The patient takes the ticket to the front desk to recharge, and then goes to the pharmacy to get the medicine. The pharmacist swipes the card to take the medicine according to the patient's ticket to complete the drug delivery and charging process
2.2 feasibility analysis
The system mainly includes four parts: basic data maintenance, basic business, database management and information query.
1. The basic data maintenance part shall include providing the administrator with ways to add, modify and maintain basic data. For example, add and modify hospitals, check in and move out patients or change wards, and manage the basic equipment in the hospital.
2. The database management part is the management of this database, including doctor, patient details and so on.
3. The technical feasibility analysis of the system: the system maintenance includes doctor and patient information retrieval and database information maintenance.
4. Feasibility analysis of system technology:
Based on jvm and mysql, the system can run in windows and Linux operating systems, and can provide a stable running environment for the system. The system should be said to have the necessity of development.
The system is mainly composed of two parts: management, maintenance and query.
3.1 conceptual structure design
3.1.1 abstract the entities of the system
Target objects involved: patients, departments, doctors, pharmacists, front desk cashier, drugs, registration form, prescription information
3.2 design sub-E-R diagram
3.3.1 global E-R diagram
4.1 logic structure design
| Patient history | (ID number, name, gender, age, case); |
| Drug storage record | (drug number, drug name, purchase price, selling price, drug quantity, production date, validity period and storage location); |
| register | (registration number, patient number, gender, registration department, doctor number); |
| charge | (toll collector number, toll collector name, patient number, drug number, quantity and amount); |
| doctor | (doctor's employee number, doctor's name, doctor's gender, doctor's age, Department, telephone); |
| prescription | (doctor, employee number, drug number, quantity); |
| payment | (payment number, charge number, price); |
5.1 database physical design and Implementation
register
Physician information
Patient information
Drug information
Payment information
Drug receipt
Prescription information
Payment voucher
6. Data operation requirements and Implementation
6.1.1 data query and update
select * from register where register.is_delete=0;
select * from doctor where doctor.is_delete=0;
select * from patient where patient.is_delete=0;
SELECT * FROM drugs WHERE is_delete=0;
select * from charge WHERE is_delete=0;
select * from PGM WHERE is_delete=0;
select * from recipel WHERE is_delete=0;
select * from pay WHERE is_delete=0;
6.1.2 realize the warehouse in and warehouse out management of drugs;
1. INSERT INTO drugs(drug_id, drug_name, drug_price, drug_quantity, drug_storage, drug_date, usefull_life)
2. VALUES ('1000237', 'Elixir of immortality', 9999.00, '821', 'C-8-291', '2021-09-01', '2022-09-01');
3.
4. SELECT * FROM drugs WHERE drug_name = 'Elixir of immortality' AND is_delete=0;
1. UPDATE drugs SET drug_name = 'Smart grass' WHERE drug_id = '1000237'; 2. 3. UPDATE drugs SET IS_DELETE=1 WHERE drug_name='Smart grass';
Corresponding java backend implementation
1. Addition, deletion and modification
2. package com.vector.hospital_information;
3.
4. import com.vector.config.SpringConfiguration;
5. import org.junit.Test;
6.
7. import org.junit.runner.RunWith;
8. import org.springframework.context.ApplicationContext;
9. import org.springframework.context.annotation.AnnotationConfigApplicationContext;
10. import org.springframework.stereotype.Component;
11. import org.springframework.test.context.ContextConfiguration;
12. import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
13.
14. import javax.annotation.Resource;
15. import javax.sql.DataSource;
16. import java.sql.Connection;
17. import java.sql.PreparedStatement;
18. import java.sql.SQLException;
19.
20. @RunWith(SpringJUnit4ClassRunner.class)
21. @ContextConfiguration(classes = {SpringConfiguration.class})
22. @Component("Update_test")
23. public class Update_test {
24. @Resource(name = "dataSource")
25. DataSource dataSource;
26. @Resource(name = "Update_test")
27. Update_test modify;
28. @Test
29. /**
30. * Test addition, deletion and modification
31. */
32. public void TestUpdate() throws SQLException {
33.
34. /**
35. * Test add data
36. */
37. String sql1 = "INSERT INTO drugs(drug_id,drug_name,drug_price,drug_quantity,drug_storage,drug_date,usefull_life) VALUES (?, ?, ?, ?, ?, ?, ?)";
38. modify.update(sql1,"1000237", "Elixir of immortality", 9999.00, "821", "A-8-291", "2021-09-01", "2022-09-01");
39.
40. /**
41. * Test modification data
42. */
43. // String sql2 = "UPDATE drugs SET drug_name = ? WHERE drug_id = ?";
44. // modify.update(sql2, "smart grass", "1000237");
45. // /**
46. // * Test delete data
47. // */
48. // String sql3 = "DELETE FROM drugs WHERE drug_name=?";
49. // modify.update(sql3, "smart grass");
50. }
51. /**
52. * Wang Jiahui
53. * General addition, deletion and modification
54. * @param sql
55. * @param args
56. * @throws SQLException
57. */
58.
59. //General addition, deletion and modification operations
60. public void update(String sql, Object... args) throws SQLException {//The number of placeholders in sql is consistent with the length of variable parameters
61.
62. Connection conn = null;
63. PreparedStatement ps = null;
64. //ApplicationContext app = null;
65. try {
66. //app = new AnnotationConfigApplicationContext(SpringConfiguration.class)
67. //1. Get database connection
68. conn = dataSource.getConnection();
69. //2. Precompile the sql statement and return the PreparedStatement instance
70. ps = conn.prepareStatement(sql);
71. //3. Fill placeholder
72. for (int i = 0; i < args.length; i++) {
73. ps.setObject(i + 1, args[i]);
74. }
75. //4. Execute sql statement
76. ps.execute();
77. System.out.println("Record added successfully");
78. } catch (Exception e) {
79. e.printStackTrace();
80. } finally {
81. //5. Closure of resources
82. conn.close();
83. }
84. }
85.
86. }
1. Sql query
2. package com.vector.hospital_information;
3.
4. import com.vector.config.SpringConfiguration;
5. import com.vector.test.DataSourceTest;
6. import org.junit.Test;
7. import org.junit.runner.RunWith;
8. import org.springframework.stereotype.Component;
9. import org.springframework.test.context.ContextConfiguration;
10. import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
11.
12. import javax.annotation.Resource;
13. import javax.sql.DataSource;
14. import java.lang.reflect.Field;
15. import java.sql.*;
16.
17. @RunWith(SpringJUnit4ClassRunner.class)
18. @ContextConfiguration(classes = {SpringConfiguration.class})
19. @Component("PreparedStatementQueryTest")
20. public class PreparedStatementQueryTest {
21.
22. @Resource(name = "dataSource")
23. DataSource dataSource;
24. @Resource(name = "PreparedStatementQueryTest")
25. PreparedStatementQueryTest queryTest ;
26.
27.
28. @Test
29. /**
30. * Test query
31. */
32. public void TestQuery() throws SQLException {
33.
34.
35. /**
36. * Test query a record
37. */
38. String sql = "SELECT * from drugs where drug_name=?";
39. Drugs drugs = queryTest.getInstance(Drugs.class,sql,"Elixir of immortality");
40. System.out.println(drugs);
41.
42.
43. }
44. /**
45. * Wang Jiahui
46. * For general query operations on different tables, a record in the table is returned
47. * @param clazz
48. * @param sql
49. * @param args
50. * @param <T>
51. * @return
52. */
53. public <T>T getInstance(Class<T> clazz,String sql,Object ...args) throws SQLException {
54. Connection conn = null;
55. PreparedStatement ps = null;
56. ResultSet rs = null;
57. try {
58. conn = dataSource.getConnection();//Load database
59. ps = conn.prepareStatement(sql);
60. for (int i = 0; i < args.length; i++) {
61. ps.setObject(i + 1, args[i]);
62. }
63. //Execute, get result set
64. rs = ps.executeQuery();
65. //Get metadata of result set
66. ResultSetMetaData rsmd = rs.getMetaData();
67. //Get the number of columns
68. int columuCount = rsmd.getColumnCount();
69. if (rs.next()) {
70. T t = clazz.newInstance();
71. for (int i = 0; i < columuCount; i++) {
72. //Get the column value of each column through ResultSet
73. Object columnValue = rs.getObject(i + 1);
74. //Get the column name of each column through ResultSetMetaData
75. //Get column name of column: getColumnName() -- not recommended
76. //Get alias of column: getColumnLabel()
77. String columnLabel = rsmd.getColumnLabel(i+1);
78. //Through reflection, assign the property value of the specified object name columnName to columnValue
79. Field field = clazz.getDeclaredField(columnLabel);
80. field.setAccessible(true);
81. field.set(t, columnValue);
82. }
83. return t;
84. }
85. } catch (Exception e) {
86. e.printStackTrace();
87. } finally {
88. conn.close();
89. }
90.
91. return null;
92. }
93.
94.
95. }
6.1.3 realize the management of departments, doctors and patients;
(1) Logical addition, deletion and modification
1. INSERT INTO register(r_num, r_patient_id, r_sex, r_dept, r_name)
2. VALUES ('222', '411282xxxxxxx1182', 'female', 'Anorectal ', 'Chen Siyu');
3.
4. SELECT * from register where r_patient_id='41128220230304554X WHERE IS_DELETE=0';
5.
6. START TRANSACTION;
7. BEGIN;
8. UPDATE patient SET p_name = 'Patient 1' WHERE p_atient_id = '41128220230304554X AND IS_DELETE=0;
9. UPDATE register SET r_name = 'Patient 1' WHERE r_patient_id = '41128220230304554X' AND;
10. IS_DELETE=0;
11.
12. UPDATE register SET IS_DELETE=1 WHERE r_num='222';
Java Related transaction submission core code
1. try {
2. //app = new AnnotationConfigApplicationContext(SpringConfiguration.class)
3. //1. Get database connection
4. conn = dataSource.getConnection();
5. conn.setAutoCommit(false);
6. //2. Precompile the sql statement and return the PreparedStatement instance
7. ps = conn.prepareStatement(sql);
8. //3. Fill placeholder
9. for (int i = 0; i < args.length; i++) {
10. ps.setObject(i + 1, args[i]);
11. }
12. //4. Execute sql statement
13. ps.execute();
14. conn.commit();
15. System.out.println("Record added successfully");
16. } catch (Exception e) {
17. conn.rollback();
18. e.printStackTrace();
19. } finally {
20. //5. Closure of resources
21. conn.close();
22. }
23. }
(2) Cascade operation 1. -- Cascade operation 2. alter table patient add 3. constraint patient_register_dept 4. foreign key(p_atient_id) references register(r_patient_id) on delete cascade; 5. DELETE FROM patient WHERE p_atient_id='41128220230304554X';
6.1.4 realize the registration management of prescriptions
1. /** 2. * Test add data 3. */ 4. String sql1 = "INSERT INTO recipel(id,doctor_id,drug_id,count,patient_name) VALUES (?,?,?,?,?);"; 5. modify.update(sql1,1,"001", "100023", "2 box","Patient 1");
6.1.5 realize charge management;
6.2 view
Create a view to query the total inventory of various drugs;
1. SELECT drug_name,sum(drug_quantity) FROM drug_view GROUP BY drug_name ;
6.3 trigger
Drug delivery operation
1. -- Create a trigger to automatically modify the inventory when drugs are received and issued; 2. # Drug delivery operation 3. delimiter $$ -- Custom end symbol 4. create trigger recipel_update 5. before insert 6. on recipel 7. for EACH ROW 8. BEGIN 9. SELECT @quantity=drug_quantity into @str 10. FROM drugs WHERE NEW.drug_id = drugs.drug_id; 11. IF @quantity <= 0 || NEW.count > @quantity THEN 12. SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning: The quantity of drugs is zero!'; 13. ELSE 14. UPDATE drugs SET drug_quantity = drug_quantity - NEW.count WHERE drug_id=NEW.drug_id AND IS_dELETE=0; 15. end if; 16. end 17. $$ -- Custom trigger end 18. delimiter ;
Before insertion
After insertion
Drug warehousing operation
Before insertion
After insertion
6.4 stored procedures
Create a stored procedure to count the number of visits and input of each department within a certain period of time;
#stored procedure
drop procedure count_people_date;
DELIMITER $$
CREATE PROCEDURE count_people_date(
#IN @begin_date datetime, which is wrong
IN begin_date datetime,
IN end_date datetime
)
BEGIN
SELECT r_dept 'Department',count(*) 'Number of visits'
FROM register
WHERE update_time BETWEEN begin_date AND end_date AND is_delete=0
GROUP BY r_dept ;
end $$
DELIMITER ;
CALL count_people_date('2021-12-04','2021-12-05');
7. Benchmarking
Pressure test:
Time delay test:
8. Summary
For this course design, considering that things should be done perfectly once, open source, design, optimization, debugging, testing and comparison should be done for him. Of course, when operating the database, the most difficult ones are
1. Trigger because I use mysql, which is highly isolated from sql service. This part is completely different. It takes a lot of effort to migrate. There are many problems that have been explored for a long time. Some problems can not be solved by stackoverflow, Baidu and csdn. For example, I use the update trigger, but I can't insert it in the trigger! It bothered me for a long time. I couldn't do anything to find the leaders in various dba groups. In short, some of them took a long time to solve and were very frustrated. Although I was frustrated, I also gained a lot. I had a deep understanding of basic SQL operations, MySQL and SQL services
2. New ideas at the same time, in the process of designing the database, it also triggered many new ideas. With new ideas, the eyes suddenly opened up. The logical deletion idea solves the problem of one to many, pulling one hair and moving the whole body. It also ensures the permanent storage of data
3. Data delay test under Linux. I thought it was easy to do by two shell automation scripts. However, errors also occurred frequently. But in the process of solving the problem, I learned about sh -x script debugging. This allowed me to catch errors. I completed data collection and qbs test in time dimension
9. References
[1] High performance MySQL: version 3 / (US) Schwartz,B. (US) Zaitsev,P., (US) Tkachenko,V; Translated by Ning Haiyuan et al. - Beijing: Electronic Industry Press, may 2013. Original title: High Performance MySQL,Third Edition
[2] Introduction to database system / edited by Wang Shan and SA Shixuan. - 5th Edition. - Beijing: Higher Education Press, September 2014, ISBN 978-7-04-040664-1
[3]java development manual crystallization of collective wisdom of community developers - (Huashan version) v1.5.0. - Hangzhou: Alibaba, June 2019
Write at the end
Open source connection ----->>>Click here