I am a doctor and I'm creating a new List system for my Orthopaedic team in hospital. The goal is to create a robust list system which can manage the emergency and elective intake of patients of various consultants.
I'm rebuilding an existing database which I've advanced quite far but realised limitations of due to the fundamental design limitations.
Main tables description of purpose:
tblPatientDatabase: core data about the patient is stored DOB etc
tblPatientEpisode: data pertaining to each patient episode... Patients can have multiple episodes all of which start and stop.... whilst their episode is open they become an active record on the database "active".
tblDailyrecords: for each day a patient is in hospital various data is recorded such as blood tests, clinical progress information, outstanding jobs, and other parameters pertaining to the level of severity of the patient status etc. This data is specific to each patient and specific to each episode.
tblOperations: patients can undergo undergo operations which take place within a given patient episode - I have therefore linked
tblConsultants: each patient must have an assigned consultant for every episode that they have.
tlbTeamInfo: each patient is assigned to a Team - each team works for a specific consultant or consultants however sometimes teams look after patients belonging to consultants were not are not in their team.
tblWard: patients are admitted to specific wards which relate to specific episodes. Obviously each ward will have multiple records of different patients have their "episodes" within the wards... However patients also move between wards during "episodes".
I've created this JPEG demonstrating the proposed relationships - you can see the essence of the tables as well as some of the fields in the tables i'm creating. The sub-constituents of the individual tables are not that important at this stage
Main Issues:
1. Please can someone tell me if this is a correct start to the database based on the information I've provided in my description of the tables?
2. I'm conceptually struggling with the idea that when patients move between wards and consultants.... That they would need a new record - in my present system it seems that only one consultant and ward can be ascribed to each episode... Can someone suggest a solution to this?
3. Is there a more elegant way to deal with the relationship between the consultants and the teams? than the one that I have currently proposed? It needs to be as generic as possible as I'd like to use this database in as wide number of settings as possible...