I'm creating a database to record the details of anaesthetic/surgeries for an operating theatre. There is already an existing logbook written with Filemaker Pro that needs updating and that is the point of this project: to create a new logbook which is less prone to crashes/data loss (the old one is) and to implement a few new features.
The template will be built off the original database because it is important to be able to import and export data to and from the old application. Import/export is via a .csv file so as long as I can create .csv files that match the old template there shouldn't be too much of an issue.
The original database has some structures which I don't think would exist in a database that is normalised to 3rd normal form, but which (unusually!) I think are reasonable to have. This is my dilemma!
This is the existing case table format, with some explanations as to what they represent (where necessary):
CaseNumber - Primary Key
Date of operation
User 1 - for general notes
User 2 - for general notes
Speciality - I'm proposing this as a foreign key
Operation - I'm proposing this as a foreign key
Anaesthetic 1 - details of oxygen delivery (intubated or not, etc)
Anaesthetic 2 - details of nerve blocks (epidural for example)
Procedure 1 - extra procedures, eg chest drain, arterial line
Procedure 2 - extra procedures
Procedure 3 - extra procedures
Notes - other notes
I'm proposing the following table in addition to that above:
SurgeryID - Primary key
So, that's how it stands. The database is for recording the operation and anaesthetic. It is not supposed to be a patient record so normalisation of the patient details is not important. Each case stands on its own.
Should I be normalising this database any further? I've already decided to create tblSurgeries as there are so many different surgeries possible. The only part of it that looks like it really needs normalising are the 3 Procedures, and even then I'm thinking about leaving them as they are. The User 1 and 2 fields are a leftover from the original databse - they were intended for people to just add extra notes about a case if they needed to. I don't really see the need for them since there is already a Notes field. They only need to stay because some people may have recorded data in them.