A Database Design Question

[xProgrammer]

Hi all, I am debating with myself (surely a sign of madness) over a design question to do with an evolving medical application.

There is a patient file with details of all the patients. I have created a doctor table for doctors who send patients to the clinic. But they can be patients also.

I also need to store info on the doctors that work at the clinic.

I am thinking that maybe I should make the patient file effectively a people file and use flags to indicate whether a person is also a doctor and or is a staff member and then use indexes to enable queries on doctors or staff doctors etc to proceed nearly as quickly as if they were in separate tables. My index count will go up by 2 so time to add a patient will increase slightly.

Some questions:

Can I conditionally add records to an index? It would be wonderful if a person record that will only function as a patient could just update the appropriate indexes (primary key, name, date of birth) whilst a person record marked as a doctor could also add to the index of doctors, and similarly for a staff member.

Has anyone implemented a design like this I wonder?

Any thoughts you may have would be appreciated.

Regards
xProgrammer

[nageswaragunupudi]

I am thinking that maybe I should make the patient file effectively a people file and use flags to indicate whether a person is also a doctor and or is a staff member and then use indexes to enable queries on doctors or staff doctors etc to proceed nearly as quickly as if they were in separate tables

This would be my personal approach too.

Can I conditionally add records to an index? It would be wonderful if a person record that will only function as a patient could just update the appropriate indexes (primary key, name, date of birth) whilst a person record marked as a doctor could also add to the index of doctors, and similarly for a staff member.

Conditional indexes are possible in RDDs like DBFCDX.
Personally I advice not to do conditional indexes. Does not really save time. Conditional indexes are not used for optimization.
In case of RDMS use WHERE clause and in case of RDD use filters ( optimized )

[xProgrammer]

Thanks for your advice. If updating the extra indexes doesn't impact much on performance then I can do all the "filtering" by including the flag in an index. If, for example, I have an IsDoctor field which say contains a "D" for a doctor and " " otherwise then to get all doctors named "Smith" I just have to search on "DSMITH" given that I maintain an index on Upper( IsDoctor + Surname).

It is an approach I have thought about a number of times but never implemented to date.

Regards
xProgrammer

[Otto]

I am sure you thought about protection. I report this because I read last week in our newspapers about a scandal that in one of our clinics doctors and stuff members looked up other doctors and stuff members folders.
Best regards,
Otto

http://tt.com/csp/cms/sites/tt/Nachrichten/325026-6/kollegen-in-klinik-ausspioniert.csp

[James Bott]

You are describing the concept of roles. A person may have one or more roles; patient, doctor, dept head, vendor, etc.

Using OOP you can create a classes that automatically handle this. These classes would also insulate the rest of your code from the actual databases so you could change the configuration of the databases later without breaking all your existing code.

There is a good discussion of roles in the book "Business Engineering With Object Technology." There is link to it on my website here http://www.gointellitech.com/program.htm

I'm sure you can also Google "OOP roles" and find more information about roles.

Regards,
James

[xProgrammer]

Hi Otto

Nice to hear from you. An important issue that needs careful handling. In an emergency information may need to be accessed that would contravene what would normally be available to a doctor. My best way around this is to allow access after a screen that warns that if you continue your access will be recorded and checked.

Regards
Doug

[xProgrammer]

Hi James

Yes, reading about roles is probably one of the antecedents of the idea. Pretty much all my code is OO. My code generator is OO and generates OO code. Although, at this stage, it could be held that the templates that the generator generates from are not.

To give some idea, my application includes the following classes dealing with a patient:

PatientList
PatientListScreen which inherits from TListScreen which inherits from TScreen
PatientSearchByName and PatientSearchByDOB both of which Inherit from TSearchScreen which inherits from TScreen

Patient which inherits from TSingleItem
PatientKeySearch which inherits from TKeySearchScreen which inherits from TScreen
PatientViewEditScreen which inherits from TViewEditScreen which inherits from TScreen

Note: these are all generated from a design template in xml.

TSingleItem is quite useful. You would think there should be a matching TListOfItems class but in practice there doesn't seem to be much, if anything, it could contribute. TSingleItem handles some of the "standard" fields I use, as well as understanding primary key field. I have an active flag, as well as recording when the record was last updated, by what user, and what they did (insert or update) for all data tables.

Regards
Doug

[frose]

regarding data security, you have to encrypt/decrypt your informations with different keys!

Don't use other technologies, e.g. roles or other, for this purpose, they are not safe.

For example: For an admin it's normally not a big problem to get the Master-role, but if he hasn't the suitable decryption keys, he has NO chance to get the datas!

On the other hand, it's very easy and performant to work with roles, e.g. we have a big (Lotus Notes) database with CRM datas. The sales staff which is responsible for one region, 'see' and replicate only the documents of his region and not all the others! But (of course) the database itself is encrypted when it resides on local machines and the data traffic over the internet (replication port) is encrypted too.