SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    239
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code mysql:
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    , 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    , 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

  6. #6
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    239
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SubEffect View Post
    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

    Code mysql:
    asa varchar ( ) not null
    put in a number between the brackets that is sufficient for the longest word you would enter.

    hth

    bazz

  8. #8
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    239
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SubEffect View Post

    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

  10. #10
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    239
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    239
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •