Hospital outpatient service Reservation System

1
Hospital outpatient service Reservation System
from 605 team
??? ??? ??? ??? ???
2
System Outline
Hospital outpatient service reservation
management is a brand-new process and attempt for
the medical system application information
technology and products. It has the long-term
characteristic, the complexity, timeliness and
polytropic Because of this, hospital outpatient
service registration system cannot be short,
simple, seal, static system, but is one open, the
request is more prompt, precise, in the
application depth and the breadth, the change and
develop nimbly along with the time.
3
Functional module
4
Patient
5
(No Transcript)
6
(No Transcript)
7
(No Transcript)
8
Fill detail of Reservation
9
Doctor
10
(No Transcript)
11
Backstage
12
??
??
13
Problem 1Scheduling information storage
Problem 2Deadlock of table creating
Problem 3Implementation of trigger
Problem 4Index creating for table Schedule
Problem 5Change between relative time and
absolute time
14
Problem 1Scheduling information storage

• Suppose there are 30 departments in our hospital
  ,and each department has about 15 doctors, each
  doctor is mapped to one unique scheduling
  record. One problem is how to manage all the
  scheduling information?

15
Problem 1Scheduling information storage

• The seven days of a week is divided to 70
  available periods, each period has unique number.
• Responding to each number, each doctor will be
  Y or N.
• The reservation table also record the max number
  of patients if the status is Y

16
Problem 1Scheduling information storage

• The storing schema for the different doctors is
  the same and what make the difference is the
  contents.
• We encounter with a problem about the
• typevariablevalue . There is no variable
  in our database system, which has been told by
  Professor Meng.

17
Problem 1Scheduling information storage

• In order to save the storage space, our first
  
• solution to the problem is adding a column
  like a bit string
• Each bit corresponds to a period num,
• using 1 or 0 to indicate the status whether
• the doctor is available.

18
Problem 1Scheduling information storage

• But this brings a problem. When we get the
  information from the database, we should break
  the bit string in our program to get the specific
  period we want .
• After discussion with assistants ,we find that
  45070 records is small for our powerful DBMS.

19
Problem 1Scheduling information storage

• So we use the second method
• building a relatively large table Schedule to
  store the information, in which the primary key
  is (DctNo , PeriodNo), and we build indexes on
  the table to accelerate our query.

20
Problem 2Deadlock of table creating
Reservation Reservation Reservation Reservation
RsvNo DctNo PeriodName OfficeNo
Doctor Doctor Doctor
DctNo DeptNo OfficeNo
Schedule Schedule Schedule
PeriodNo DctNo PeriodName
Office Office
OfficeNo DeptNo
Department Department
DeptNo DctNo
Chief
21
Problem 2Deadlock of table creating
Doctor(DeptNo) Department(DeptNo)
Doctor(OfficeNo) Office(OfficeNo)
Department(DctNo) Doctor(DctNo)
Office(DeptNo) Department(DeptNo)
though record all informations,
there are dead circles.
All because
In the function of DBMS, there are not enough
mechanism to satisfy the actual need of mutual
references .
22
Problem 2Deadlock of table creating
So, we have to cut one link between two tables
within the dead circle. To compensate the
information lost, we could set some mechanism.
Remove the attribute DeptNo from table
Department. In actual situation, only a doctor
can be some chief.
23
Problem 3Implementation of trigger
Without Time Trigger A manager is needed
to delete the Reservation table at 2400
everyday. With Time Trigger when its
2400, the Reservation Table is deleted
automaticly.
24
Problem 3Implementation of trigger
Cute idea! But ? hard to put the idea into
practice ? better to write a store
procedure, and execute by Sql server Agent in the
work when its 2400 everyday
25
Problem 3Implementation of trigger

• ? What we need is time triggering.
• ? Existing event triggering mechanism is not
  suitable in our system.
• In a word, in our system, we do not use the
  trigger eventually.

26
Problem 4Index Creating for Table Schedulewhy
to create a index

• Schedule( PeriodNo, DctNo, PeriodName,
  PeriodState,PeriodCount, PeriodMax)
• (PeriodNo, DctNo) is the primary key
• There are 480 doctors in this hospital
• and 70 periods per week
• we have 48070 33600 tuples in Schedule!
• A large table
• Every patient who reserves a doctor will have to
  query the table.
• Suppose we have 3000 selections per day.
• A index on table Schedule is badly needed.

27
Problem 4Index Creating for Table Schedule
choose attributes which needs a index

• Schedule( PeriodNo,DctNo,PeriodName,
• PeriodState,PeriodCount,PeriodMax)
• A Patient can search a doctor according to his
  convenient time (based on PeriodName) or doctors
  name (based on DctNo)
• Both selections are of high frequency
  (approximately the same)
• There is lower search frequency and higher update
  frequency on other attributes
• PeriodName should have a index
• DctNo should have a index

28
Problem 4Index Creating for Table Schedule
choose primary index

• PeriodName should have a primary index and
• DctNo should have a secondary index, according
  to
• Types of Queries PeriodName Range Query
• DctNo Point Query
• Repeating Values PeriodName 480
• DctNo 70
• Searches on PeriodName are more likely to return
  more tuples than searches on DctNo
• Maintenance Cost PeriodName Hardly changes
• DctNo Changes sometimes
• PeriodName has lower update frequency than
  DctNo

29
Problem 4Index Creating for Table Schedule

• Table Schedule 33600 tuples
• Query
• Search all available doctors from 800 to 1100
  on Monday.
• query time
• no index128
• primary index on PeriodName
• and secondary index DctNo29

30
Problem 5Change between relative time and
absolute time

• Doctors schedule repeats per week,so the
  relative time in the schedule corresponds to the
  absolute time which changes every week.
• e.g. the relative time Saturday 800-900
  correspond to 2008-1-5 800-900 this
  week,while it corresponds to 2008-1-12
  800-900 next week.

31
Problem 5Change between relative time and
absolute time

• The patient should be displayed with the
  absolute time for fear that they are confused by
  the relative time.
• How to make the relative time information in
  Schedule table represents the correct absolute
  time has become a problem.

32
Problem 5Change between relative time and
absolute time

• Add an additional attribute in Schedule table
  and let this attribute change per week?

Redundance and huge cost to update
33
Problem 5Change between relative time and
absolute time

• Transfer the task to the application
  program,use the program to get the current time
  and then work out the absolute time?

A burden to program and low efficiency
34
Problem 5Change between relative time and
absolute time

• Our solution Add an additional small table to
  record the correspondence between them.
• Advantages
• Use low space cost to record the information.
• Update of the small table needs low cost.
• When we need to display the absolute time to the
  patient,we only need to join the two tables.

35
As the saying goes, Three smelly tanners go
against a Zhuge Liang. Much less we are such
an excellent team. Regardless of individual
strength has any difference, In discussion, the
inspiration colliding the spark causes us all to
benefit greatly.
TEAMWORK !
36
Thank you