11 Replies Latest reply: Jan 22, 2013 12:49 PM by marksmithusa RSS

    Advice on a National Hospital Database design

    905952
      HI Everyone, Am designing a National Hospital Database as part of my undergraduate project, Am making use of RHEL 4 and Oracle 11.1.0, I planned having two tables "HOSPITAL" & "PATIENT", the database is expected to have millions of records. I just need an advice on what to do, what to consider during my design, views and triggers that may be needed to enhance the functionality of the database.

      Regrads
      Ferdicon Mary.
        • 1. Re: Advice on a National Hospital Database design
          JohnWatson
          Follow a standard system development life cycle model. Do these first:

          business analysis - what should the application do?
          systems analysis - entity-relationship modelling and data flow diagrams

          only then start thinking about tables, views, and so on. You will not graduate successfully if you jump into coding as your first start. I am sure you have been taught this already.
          • 2. Re: Advice on a National Hospital Database design
            Billy~Verreynne
            Architecturally - the best designs are those where the database implements an abstraction layer. This layer contains the business rules and business logic. Does the actual SQL statements. Makes the changes to the data model.

            The user applications (of which there can be several, from lightweight front-desk web-based applications, to heavy back-office analytical applications) all use the same abstraction layer. Which means that no single application can get business rules wrong, or implement business logic differently.

            This ensures consistency across applications.

            It also makes applications less complex, faster to develop, and easier to maintain. A change in a business rule? The abstraction layer/application interface is changed - and the change is global across all applications, without having to touch a single line of code in any application.

            Database/SQL performance problems? Change in data model? Utilising new database features?

            These all can be addressed fully and comprehensively via the abstraction layer. Again without touching a single line of application code.

            Cost is also cheaper. Application developers do not need to understand the database technology, features and so on. (they almost never do in any case) They use an API - and only need to understand how to use that API, and not database.

            In Oracle this abstraction layer is developed using PL/SQL packages. PL/SQL is a proper programming language - like C/C++ and Java. Do not mistake it as a scripting language. Do not think it is a mere SQL language extension like SQL-Server's T-SQL. PL/SQL is every bit as good, as robust, as powerful, as C/C++ and Java.

            These PL/SQL packages does the SQL side. Updates, inserts and deletes data. Implements business logic. Protects data and transaction integrity. Addresses performance issues. And presents a business application programming interface (API) to app users to use for app development.

            And no, database triggers seldom have any role to play in this architecture - and is considered as a bit of an archaic feature.

            I've seen this implemented (while doing data warehousing contracting in the medical aid industry) as far back as the mid 90's (Oracle 7) by vendors that wrote high-end medical application and claims processing systems for of the major hospitals and medical aid schemes in the US and abroad. This architecture is even more relevant today than back then.
            • 3. Re: Advice on a National Hospital Database design
              EdStevens
              902949 wrote:
              HI Everyone, Am designing a National Hospital Database as part of my undergraduate project, Am making use of RHEL 4 and Oracle 11.1.0, I planned having two tables "HOSPITAL" & "PATIENT", the database is expected to have millions of records. I just need an advice on what to do, what to consider during my design, views and triggers that may be needed to enhance the functionality of the database.

              Regrads
              Ferdicon Mary.
              I want to second what John Watson said. You need to focus on a business and systems analysis, getting to a entity-relationship diagram with your data model at Third Normal Form. If you are at the point of undertaking the project you describe, you should already be familiar with those concepts.

              The fact that it is a "National Hospital" database is irrelevant. The process of design is the same regardless of the application. The operating system is irrelevant. The version of Oracle is irrelevant. In fact, the use of Oracle is itself irrelevant at this point. "views and triggers that may be needed to enhance the functionality of the database" are irrelevant at this point. Those are all physical considerations to be addressed only after you have the logical design completed. The logical design must come first, and that is the same regardless of if you plan on implementing with Oracle, SQLServer, MySQL, Sybase, MS Access, dBase, IMS, or vsam files.
              • 4. Re: Advice on a National Hospital Database design
                Girish Sharma
                Since you are looking for your undergraduate project I think below link may help you :
                http://my.safaribooksonline.com/book/databases/9788131731925/hospital-management-system/app01

                Tables Description

                Following are the tables along with constraints used in Hospital Management database.

                DEPARTMENT: This table consists of details about the various departments in the hospital. The information stored in this table includes department name, department location, and facilities available in that department.

                Constraint: Department name will be unique for each department.

                ALL_DOCTORS: This table stores information about all the doctors working for the hospital and the departments they are associated with. Each doctor is given an identity number starting with DR or DC prefixes only.

                Constraint: Identity number is unique for each doctor and the corresponding department should exist in DEPARTMENT table.

                DOC_REG: This table stores details of regular doctors working in the hospital. Doctors are referred to by their doctor number. This table also stores personal details of doctors like name, qualification, address, phone number, salary, date of joining, etc.

                Constraint: Doctor’s number entered should contain DR only as a prefix and must exist in ALL_DOCTORS table.

                DOC_ON_CALL: This table stores details of doctors called by hospital when additional doctors are required. Doctors are referred to by their doctor number. Other personal details like name, qualification, fees per call, payment due, address, phone number, etc., are also stored.

                Constraint: Doctor’s number entered should contain DC only as a prefix and must exist in ALL_DOCTORS table.

                PAT_ENTRY: The record in this table is created when any patient arrives in the hospital for a check up. When patient arrives, a patient number is generated which acts as a primary key. Other details like name, age, sex, address, city, phone number, entry date, name of the doctor referred to, diagnosis, and department name are also stored. After storing the necessary details patient is sent to the doctor for check up.

                Constraint: Patient number should begin with prefix PT. Sex should be M or F only. Doctor’s name and department referred must exist.

                PAT_CHKUP: This table stores the details about the patients who get treatment from the doctor referred to. Details like patient number from patient entry table, doctor number, date of check up, diagnosis, and treatment are stored. One more field status is used to indicate whether patient is admitted, referred for operation or is a regular patient to the hospital. If patient is admitted, further details are stored in PAT_ADMIT table. If patient is referred for operation, the further details are stored in PAT_OPR table and if patient is a regular patient to the hospital, the further details are stored in PAT_REG table.

                Constraint: Patient number should exist in PAT_ENTRY table and it should be unique.

                PAT_ADMIT: When patient is admitted, his/her related details are stored in this table. Information stored includes patient number, advance payment, mode of payment, room number, department, date of admission, initial condition, diagnosis, treatment, number of the doctor under whom treatment is done, attendant name, etc.

                Constraint: Patient number should exist in PAT_ENTRY table. Department, doctor number, room number must be valid.

                PAT_DIS: An entry is made in this table whenever a patient gets discharged from the hospital. Each entry includes details like patient number, treatment given, treatment advice, payment made, mode of payment, date of discharge, etc.

                Constraint: Patient number should exist in PAT_ENTRY table.

                PAT_REG: Details of regular patients are stored in this table. Information stored includes date of visit, diagnosis, treatment, medicine recommended, status of treatment, etc.

                Constraint: Patient number should exist in patient entry table. There can be multiple entries of one patient as patient might be visiting hospital repeatedly for check up and there will be entry for patient’s each visit.

                PAT_OPR: If patient is operated in the hospital, his/her details are stored in this table. Information stored includes patient number, date of admission, date of operation, number of the doctor who conducted the operation, number of the operation theater in which operation was carried out, type of operation, patient’s condition before and after operation, treatment advice, etc.

                Constraint: Patient number should exist in PAT_ENTRY table. Department, doctor number should exist or should be valid.

                ROOM_DETAILS: It contains details of all rooms in the hospital. The details stored in this table include room number, room type (general or private), status (whether occupied or not), if occupied, then patient number, patient name, charges per day, etc.

                Constraint: Room number should be unique. Room type can only be G or P and status can only be Y or N.

                Since database design is a vast topic which really needs a good book. This wiki page gives a good start though :
                http://en.wikipedia.org/wiki/Database_design

                Regards
                Girish Sharma
                • 5. Re: Advice on a National Hospital Database design
                  Osama_Mustafa
                  http://bit.ly/10q8pPo
                  • 6. Re: Advice on a National Hospital Database design
                    marksmithusa
                    A National Hospital Database, huh?

                    Well, if most experiences are anything to go by, this is how it's usually done:

                    a) Submit bid for tender
                    b) Be as vague as humanly possible in your requirements
                    c) Accept bid from cheapest vendor, no matter whether they can spell 'HOSPITAL' or not
                    d) Vendor implements a National Firefighters Database instead
                    e) Customer rather surprised, not happy, goes public, blames vendor for everything
                    f) Vendor implements changes with ASTRONOMICALLY expensive 'customizations' as what the customer wanted 'wasn't in the initial requirements'
                    g) Customer gets the bill. LOLs - 'we're not paying THAT!' Goes public and explains why project cost is 250% over budget ('vendor's fault - con-artists, shameful exploitation of government contract', etc)
                    h) In a couple of years, submit a new bid for tender and refuse bids from current vendor even if current vendor now knows what you want and has implemented 95% of the system
                    i) Spend two years for current vendor to 'handover' to new vendor'
                    j) Rinse and repeat
                    • 7. Re: Advice on a National Hospital Database design
                      Billy~Verreynne
                      Amazing how this keeps on repeating itself in different industries...
                      • 8. Re: Advice on a National Hospital Database design
                        905952
                        Thank You Very much, this is exactly what I needed.
                        • 9. Re: Advice on a National Hospital Database design
                          jgarry
                          I've seen a variant where around step d or e, users hire a couple of guys to get it all working from scratch (in C with inline coding, no less), it succeeded where user acceptance testing of huge project failed. Of course, I was the third guy who had to maintain it after the first two left.
                          • 10. Re: Advice on a National Hospital Database design
                            EdStevens
                            consultant: "I completed the audit of your new $150 million software system. I recommend that you scrap the entire thing. Your normal software system would be a clever combination of zeros and ones. Yours is all ones. My company can sell you all the zeros you need, but you'll have to arrange them yourself.

                            PHB to Dilbert: "When you have a few minutes I have a little assignment for you."
                            • 11. Re: Advice on a National Hospital Database design
                              marksmithusa
                              Things you'll never hear a consultant say:

                              - "You're right! We're charging WAY too much for this!"
                              - "How about paying me based on the success of the project?"
                              - "Everything looks OK - you don't need me here."

                              Apologies to any genuinely-useful consultant who might read this ;)