Building a database without fully normalising - is it every alright to do so?

[SubEffect]

Background

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.

Problem

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):

tblCase
CaseNumber - Primary Key
Date of operation
Start time
End time
Patient reference
Patient Sex
Patient Age
Patient DoB
ASA
Priority
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
Anaesthetist
Supervision
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
Incidents


I'm proposing the following table in addition to that above:

tblSurgeries
SurgeryID - Primary key
Speciality
Operation


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.

Thoughts anyone?

[r937]

your case table is fine

i don't see how the surgeries table will relate to the case table, because you've proposed a SurgeryID as the primary key, but in the case you've got Speciality and Operation as proposed foreign keys, so are these going to link to separate entries in the suregieries table, or...?

otherwise the rest looks okay, you can leave it as is

[IBazz]

I expect that you could have more than one operation per case. Thats why I would suggest considering a table for the patient details and one for the operation details and operation-related data.

Only a suggestion - rudy is the expert.

I left out the values for the varchars ( ) which could be (32) for example.
I annotated the columns I don't understand the meaning of.

create table if not exists case
( case_number int not null // could be varchar if combination of alphanumerics
, patient reference varchar ( ) not null
, sex char (2) not null
, dob datetime not null
, ASA (dunno what this means)
, priority (dunno what this means)
, user 1 text  - for general notes
, user 2 text - for general notes
PRIMARY KEY (case_number)
) engine=InnoDB default charset=Latin1;
 
 
create table if not exists Surgeries
( CaseNumber int not null
, Date of operation datetime not null
, Start time datetime not null
, End time datetime not null
, Speciality varchar ( ) not null
, Operation varchar ( ) not null
, Anaesthetist varchar ( ) not null
, Supervision varchar ( ) not null
, Anaesthetic 1 tinytext not null - details of oxygen delivery (intubated or not, etc)
, Anaesthetic 2 tinytext not null - details of nerve blocks (epidural for example)
, Procedure 1 text default null - extra procedures, eg chest drain, arterial line
, Procedure 2 text default null - extra procedures
, Procedure 3 text default null - extra procedures
, Notes blob not null - other notes
, Incidents text default null
unique index (case_number, date of operation, start_time) 
) engine=InnoDB default charset=Latin1;

hth
bazz

[r937]

, sex CHAR (2) NOT NULL

why 2 characters? are you expecting a proliferation of codes here?

, dob DATETIME NOT NULL

do you really expect to record the time?

, ASA (dunno what this means)

a-seat'll-sally-sillic acid, silly

[IBazz]

, sex CHAR (2) NOT NULL

why 2 characters? are you expecting a proliferation of codes here?

depends on the purpose of the record I suppose

, dob DATETIME NOT NULL

do you really expect to record the time?

nope, error on my part

, ASA (dunno what this means)

a-seat'll-sally-sillic acid, silly

I see now

[SubEffect]

Thanks for the input guys.

I've just put the surgery ID in to make it easy for myself. In theory the operation will be unique and so could be the primary key, but this will be a user modifiable field in the case table as operations often differ from the absolute norm. This also applies where there is more than one operation per case - it's still 1 case even if the patient has a hip and a wrist fixed (for example). tblSurgeries only contains a (loooong) list of common operations - appendicectomy for example - that make it easy for the users to get something a least similar to what has actually been done. Of course many times this will be exactly what has been done, but flexibility is important.

ASA is American Society of Anesthesiologists. Its a grading system for determining how systemically unwell a patient is prior to anaesthesia.
Priority is "Emergency", "Urgent", "Scheduled" or "Elective". Its how desperately the case needed doing.

[IBazz]

Thanks for the input guys.
This also applies where there is more than one operation per case - it's still 1 case even if the patient has a hip and a wrist fixed (for example).

OK, if you were to use my suggestion for those two tables (mentioned earlier), as a base, I think case_number could be the fk in the second table for when a patient has >1 operation/procedure

tblSurgeries only contains a (loooong) list of common operations - appendicectomy for example - that make it easy for the users to get something a least similar to what has actually been done. Of course many times this will be exactly what has been done, but flexibility is important.

(were it mine), I would rename your tblSurgeries (the one with the looong list), to 'available_procedures' since it seems like you use this table just for populating a drop down selction. In any event it is good to get away from naming them 'tbl_whatever'. It seems to be a common ailment and I had it too until rudy suggested I stop.

ASA is American Society of Anesthesiologists. Its a grading system for determining how systemically unwell a patient is prior to anaesthesia.
Priority is "Emergency", "Urgent", "Scheduled" or "Elective". Its how desperately the case needed doing.

OK, its column should be

asa varchar ( ) not null

put in a number between the brackets that is sufficient for the longest word you would enter.

hth

bazz

[SubEffect]

Thanks for the input again. I know SQL (courtesy of Oracle), but I'm probably going to be using Access for this at the moment anyway as everyone at work can use it. This may evolve into a php/mySQL/(AJAX) thing at some point. The reason I'm asking here is because as a member and user I know you guys know your databases better than anyone else (that I know on the net, for sure).

I don't think going with 2 tables is sensible because you end up over complicating the DB for very little gain. Surgeries are recorded as the event anyway, not as 2 procedures. I've checked this with the end users and the single table is the best way.

I will certainly be using the available_procedures table only as a drop-down select, yes.

There is one other table but it will also be for a drop-down select - the Incidents table.

It has the form:
incident_type
incident_name

Combined the incident type and name make a compound primary key. Examples would be:

type: cardiovascular, name: myocardial infarction.

or

type: equipment, name: ventilator failure.

[IBazz]

I don't think going with 2 tables is sensible because you end up over complicating the DB for very little gain. Surgeries are recorded as the event anyway, not as 2 procedures. I've checked this with the end users and the single table is the best way.

Maybe I am not grasping your db structure correctly but, I think when you have prodcedure_1, prodcedure_2 and procedure_3 in separate cols in the same table, it is perhaps not normalised. (I know, my table suggestion was like that too).

The end user should/will not, be aware how the db is structured so the number of tables used is to be determined only by the rules of normalisation.

I would suggest too that whilst you worry about over-complicating it for little gain, don't forget about db management and the necessity to keep thew whole thing manageable; again as determined by the rules of normalisation.

Beyond that; I shall defer to rudy or another much more experienced individual.

bazz

[SubEffect]

Yes, I was tempted to normalise the DB with regards to the procedures. That is one of the reasons I asked if its ok to not normalise. I think I'm happy with it as it is, thanks.

[r937]

yes, it's okay to have columns like foo1, foo2, and foo3...

... as long as you're willing to swallow the cost and disruption of changing your app if you ever need to add foo4

[SubEffect]

Ha ha. Of course! If I were building this to my own specs - and by that I mean from the ground up without any previous version to be taken into account or any report format to stick to - I'd normalise to 3rd NF or even BC NF.

When I finish this if they ask me to do a new version or even an online one it will be very, very different and much, much more flexible. But the users are king and I know the limits of what I'm doing.