Patient management database, Help needed for tables and relationships

[faisals]

Hi,
I am new to this post. I am a physician with interest in database designing. I have been trying to design a database for my clinic for few months but am unable to make one. I have been searching/ reading alot of info and came across this thread. Maybe someone can help me.
Actually, I want to make a database regarding ultrsound scan examinations of patients.
I have five tables.
1. Patients. (patinetid*, patientname, age, sex, address, contact no)
2. Physicians. (physicianid*, physicianname, speciality, address, contact no)
3. Scans. (Scanid*, scanname, charges)
4. Scan orders.(scanorderid*, patientid*, physicianid, scanordernumber, scandiscount, totalcharges)
5. Scanorder details.(scanorderdetailid*, scanorderid, scanid, charges, discount)

I want to have primary key for scanordernumber which wil be the patient number and should this be placed in patient table??
All the ids have been linked with one to many reltionships. Actually I am unable to set proper relationship.
So when the patient arrives he is registered with a unique number, a physian name with date added and scan ordered is entered. Sum calculated. I have done the later part with the form all designed but the relationships and primary key are all messed up.

I can post an image of relationships or blank database.

Kindly advise. Thanks in advance.

 

[KenHigg]

My suggestion is to stick to doctoring and buy an off the shelf solution - There's no money in programming. (Unless you live in India)

 

[faisals]

Thanks for reply.
I want to make a small database for my own clinic. No commercial product is available. If you can kindly help with the table structure only.

Thanks.

 

[KenHigg]

Ok. To start with one of these entities, the scan, the patient or the physician has to be at the top of the pecking order.

- If the scan is at the top, then one scan would have one patient and one physician.
-or-

- If the scan is at the top, then one scan would have one patient and one patient has one physician and one physician has many patients.

-or-
- If the patient is at the top that one patient could have many scans and one (or many?) physicians.

-or-
- If the physician is at the top, then then one physician would have many patients and many scans.

The bottom line is a set of business rules needs to be developed...

Example:
BR1 - I need to track my scans
BR2 - I need to know how many scans I do for each patient
BR3 - I need to know home scans each physician is doing
etc.

These rules need to be stated in a way that makes no mention to the software you want - Straight business needs. List every thing you can think of. And then decide if each one is 'got to have' or 'like to have'.

etc

Or you could just wing it and hope you get it right -

 

[faisals]

Thanks a lot for replying. I really appreciate it.
Sir,

I have many patients who are referred to me by many physicians.

1. Each physician can refer many patients. I got this figured out so that multiple different patients can be entered with one physician or many physicians.
2. Each patient can come many times. I can enter all the new patients with new ID, but returning patients cannot be entered under their own unique ID, as new ID is always auto entered. I think the relationship settings are probably wrong.
3. Each scan be performed for many patients or repeated on same patient or referred by many physicians.
3. I can calculate the daily, weekly or yearly number of patients and amounts.

There may be a problem with field setup in different tables.

If you think needed, I can send relationship parameters.
Moreover, I cannot understand in which table I should place the unique patient MR number.

Thanks again.

 

[DCrake]

Simple Software Solutions

Sorry to interject but...

It seams to me that if you have a table of patients who can be referred by many physicians and each patient can have many scans irrecspective of who referred them, then you need to think about the following.

Each patient when added to the Patients table will be given a unique ID, likewize each physician added to the physicians table will have their own unique ID.

The table that holds the information about the scans will have a unique id comprised of the patient ID + physican ID +the scan count in total. (or the scan count for the physician).

As well as the scans Id you will also need the patient ID and the Physician ID and the Scan ID all in seperate fields.

This will enable you to interogate your data via patient or physician. PLus this also means that patients re referred do not need adding to the patients table as they already exist and therefore all their demographic details only need to be added once. This also applies to your physicians as well.

The scans are your episodic details and as such can be linked to either patient and/or physican whilst still maintaining their own identity via their unique id.

Code Master:

 

[KenHigg]

How many scans do you do in a year?

 

[KenHigg]

Sorry to interject but...

It seams to me that if you have a table of patients who can be referred by many physicians and each patient can have many scans irrecspective of who referred them, then you need to think about the following.

Each patient when added to the Patients table will be given a unique ID, likewize each physician added to the physicians table will have their own unique ID.

The table that holds the information about the scans will have a unique id comprised of the patient ID + physican ID +the scan count in total. (or the scan count for the physician).

As well as the scans Id you will also need the patient ID and the Physician ID and the Scan ID all in seperate fields.

This will enable you to interogate your data via patient or physician. PLus this also means that patients re referred do not need adding to the patients table as they already exist and therefore all their demographic details only need to be added once. This also applies to your physicians as well.

The scans are your episodic details and as such can be linked to either patient and/or physican whilst still maintaining their own identity via their unique id.

Code Master:

I'm guessing it would be pushing it to have 5000 scans in a year. Giving that a terabyte harddrive is now in the $300 range, I'd set this up in a replication schema instead of a relational one and store ALL of the scan data in one table and have patient, scan type and referring physician information in straight lookup tables. Having all of those as related tables is just going to complicate things...

Just my opinion -

 

[faisals]

Thanks, respect you all for help.

I do about 2000 scans/ year.

Its correct that I need two table, patient and physicians. Physicians are limited, about 100 that are referring these patients. Patient are many, but are usually all referred by physicians.
I have physician table with all their name and address and speciality details.
A patient table, that shows their name/ address, age, gender etc.
I have another table scans containing the test I perform, about 30, not changing, all listed with their name and charges.
So these three tables are all ready.
All have primary keys as patientID, physicianID and scanID.

Again linking is where, I am confused.

How many tables do I need to link and do I need a separate table for new patient order as smentioned in first post.

Thanks.

 

[KenHigg]

tblScans
record_number PK (Autonumber)
scan_type FK (to record_number in tblScanTypes)
patient_id FK (to record_number in tblPatients)
doctor_id FK (to record_number in tblDoctors)
scan_date
scan_notes
etc

tblScanTypes
record_number PK (Autonumber)
short_description
etc

tblPatients
record_number PK (Autonumber)
name
age
etc

tblDoctors
record_number PK (Autonumber)
name
location
etc

???

 

[faisals]

Ok. Great.
I have four tables with relationships as mentioned. Should I check enforce referential integrity on all of them.

I suppose I do need a form for entering data. Can I make an autoform for checking.

Thanks.

 

[KenHigg]

Ok. Great.
I have four tables with relationships as mentioned. Should I check enforce referential integrity on all of them.

Here's what's going to happen with this model: Say you create 100 scan records that link to doctor1 and you store the fact that he is qualified in procedure1 and procedure2 in the doctor table.

Now lets say one day he is not qualified to do procedure1 anymore. So you change that in the doctor table. Now time goes on and you enter another 100 scan records for that doctor. How will you know if he was qualified for procedure1 when he referred the first 100 scans?

Now I know you could create more history type tables, etc., etc to do all of this. But given that the database is going to be so small, why not copy all of the doctor data over to the scan table when you create the scan record?

Now if all of this history stuff is a non-issue in all of your tables then ignore everything I just said and I would enforce ref integ but not worry about cascading updates and deletes ...

I suppose I do need a form for entering data. Can I make an autoform for checking.

Thanks.

As far as the forms I would suspect that you could accommodate all you need with a main form and some subforms -

 

[faisals]

All the doctor are from different specialities - surgery, medicine, Urology etc. They refer their patients for scans. So they need to be kept in a separate table.
As far as relationships are concerned all of them are link using referential integrity.
Now tomorrow I will mak a form to check how it works. Its 12 at night.
Thanks for the help to all.
Cheers

 

[KenHigg]

Good luck -

 

[faisals]

Hi, Guys

I have been trying to make th form this evening for my four table patient database. Infact I have added a fifth table that shows the details of scan order placed.

I have two questions.

1. How can I use a value of a field in subform to appear on the main form.

2. I am having problem with the patient name field. The tab order is set to patientname, doctorname, date, patient mrno. on the main form. So a new patient is entered without any error. When another patient with the same name is entered then the same autot patientid is given and then two names of different patients are entered in single auto number field even if I change the patient mr number. How can I fix it.

I hope I have explained the problem.
Relationships image attatched.

Thanks.

 

[faisals]

Hi,
I have almost figured out the database to work.
There is one issue with auto increment patient MR number. It is not the primary key.
I have a field set to number and I want to set the value to year YY, month mm and three digit serial number with auto increment. So the format would be 08-01-01, 08-01-02 and so on. Next month it changes to 08-02-01 and cycles again. I used this with autoincrement primary key and it worked perfectly; however I cannot change it on monthly basis.

Kindly advise.

 

[jdraw]

Hi, Guys

I have been trying to make th form this evening for my four table patient database. Infact I have added a fifth table that shows the details of scan order placed.

I have two questions.

1. How can I use a value of a field in subform to appear on the main form.

2. I am having problem with the patient name field. The tab order is set to patientname, doctorname, date, patient mrno. on the main form. So a new patient is entered without any error. When another patient with the same name is entered then the same autot patientid is given and then two names of different patients are entered in single auto number field even if I change the patient mr number. How can I fix it.

I hope I have explained the problem.
Relationships image attatched.

Thanks.

A few comments/questions concerning your model.

Normally entities are named in the Singular ie Patient vs Patients (not a big deal but for info). Have you provided for yourself a brief definition/description of these entities? Sometimes writing that brief description can highlight some uncertainties (that may lead to additional entities/attributes). Going through the definition process will help clarify your thoughts.

In Patients Entity:
-why record Age, why not BirthDate. Age is relative and can be calculated by Today - Birthdate.
- what is Mrno?

What is the difference between ScanOrderId and ScanOrderDetailId?
Is Scandiscount the same as ScanDiscount? Why is it recorded in 2 spots?
What is the difference in the 2 fields named totalcharges? Why recorded in 2 spots? How does Charges relate to totalcharges?

What is the purpose of this proposed database?
Do you have any processes or procedures defined as to how you will use this database? How do you plan to enter the data?
Will anyone else use the database besides you?

It seems to me that ScanOrders and Scans are your key business.
Can 1 ScanOrder result in many Scans? Does a ScanOrder ever not result in a Scan?

Hope these questions/comments are useful.

 

[faisals]

Reagarding your post, it appears that my batabase is again not structured properly.

MRNO is patient unique identification number generated when patient comes in. Format = 08-01-01, 08-01-02 etc.

SCanorder are oders requested by patient and scanorderdetails are complete exam details including the discounts etc.

Two scan discounts in two table are the same. Don't know what to do. Similary total charges are also same in two tables.

Totalcharges are charges including any discount given.

Purpose of the database is mentioned in post 1; however this a small clinic database. Patients are referred by doctors for scans. One patient can have many scans and one scan can be done on many patients.

Currently only I will enter the data.

If you can provide me with a better database structure, I will be thankful.

Thanks again for responding.

 

[jdraw]

Reagarding your post, it appears that my batabase is again not structured properly.

MRNO is patient unique identification number generated when patient comes in. Format = 08-01-01, 08-01-02 etc.

SCanorder are oders requested by patient and scanorderdetails are complete exam details including the discounts etc.

Two scan discounts in two table are the same. Don't know what to do. Similary total charges are also same in two tables.

Totalcharges are charges including any discount given.

Purpose of the database is mentioned in post 1; however this a small clinic database. Patients are referred by doctors for scans. One patient can have many scans and one scan can be done on many patients.

Currently only I will enter the data.

If you can provide me with a better database structure, I will be thankful.

Thanks again for responding.

Here is a database structure that deals with Doctors, Patients and Scans.

I did not include MRNO, I think PatientId handles this.

This structure supports:

A Doctor refers at least one Patient.
A Patient was referred by exactly one Doctor.
A Patient received at least one Scan.
A Scan is for exactly one Patient.

Do you have "walk in " patients that were not referred by a doctor?
You said "one scan can be done on many patients" -- What exactly do you mean? One single scan represents many patients -- I don't think so.

Let me know if the structure makes sense to you.
We can ask questions of the model to see if it can handle you various situations. This is a starting point, not a final solution.

 

[faisals]

Hi,
Thanks for the table scheme. It appears to be fine . I need to add another table which shows different type of scans offered and their charges. So it will be\;

Table scan.
SCanid
SCan name
Scan charges.
SCannotes

Now the table you mentioned as scan will become scanorder.

Table Scanorder
Scanorderid PK
Patientid FK
Doctorid FK
Scanid FK
SCanorderdate
Scandiscount
SCantotalcharges (AFter discout) Need to store the value of discount in seperate field.
SCanpaymentrecieved
SCanordernotes

Moreover I need to add a seperate patient ID in patient table that has a different format than PK patientid. I may call it patientMRno. Format 08-01-001 (YY-MM-001)

Now let me know if this addition works for following situations:
One doctor can send many patients for scans.
One scan of same type can be referred by many doctors for different patients.
One patient can have may scans, after few months or for obstertics.
One scan of same type can be done on many patients.
One patient can be referred by many doctors.

So any sugesstions on above mentioned structure. If all is well I will start with the form structure.
Moreover what should be the data type of FK. Is it number with long integer.

Suggestions are welcome.
Thanks again.