Patient management database, Help needed for tables and relationships (1 Viewer)

[jdraw]

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.

A few comments.

You wish to add a table of Scan Types. Good, I have included a revised structure that includes this.

? Do you have a list of Scan Types?
If you associate a cost with a Scan Type, do you expect the cost to change in future? Will all scans of the same type always cost the same amount? If the scans can differ in cost, then you may want to consider an EffectiveCostDate that identifies the cost of a particular scan type at a given time. I don't know your business, but if you only have one charge for a scan type, you may get into some issues with when a scan (of a certain type) was done, and the Total charges, discounts etc.
I would recommend a ScanTypeCostTable that has a Date field to show the effective date --- but again I don't know your business.

I have adjusted the structure for Scan to show the ScanChargesTotal, ScanDiscountApplied, ScanChargedAmount, ScanPaymentReceivedAmount and the ScanPaymentReceivedDate

I don't see the Scan order and the Scan as different things.

Your Notes for the different entities are not Date related. For example a Note for a Patient is one text field. Do you envision having multiple comments for a given Patient at different times/dates?

I would not recommend your patientMrNo field - especially using the date as part of the field. PatientId is a unique field that identifies this patient from all other patients. A patients information/coordinates are unique to the patientId.

 

[faisals]

Thanks for reply.

I have made a database using you db structure with few modifications. File is attached.
Few questions.

1. Relationships in sample database are a bit different than you mentioned.
., one doctor can send many patients from his clinic and one patent can be referred from different doctors. According to your diagram, doctorid is linked with patient table and then patient table is linked with scanorders. So if you can check the relationship detail. As far as I undertand your schema represent one doctor referring many patients. I am confused.

2. Scan charges are usually fixed for each scan offered. Can change if required. Total of about 30 scans with different charges. SO I have added original scan charges in scan table. These are preadded and predefined in scan table.

3. Regarding MR No it is a unique patient ID with different format given to each new patient. PatientId PK is less flexible regarding its format. I need 08-01-001 Format if possible. yy-mm-001

Thanks again for helping.
Kindly check the relationships in the attached table and seen if they are fine, becuse making a test form to enter data was not possible. poof.

 

[jdraw]

Thanks for reply.

I have made a database using you db structure with few modifications. File is attached.
Few questions.

1. Relationships in sample database are a bit different than you mentioned.
., one doctor can send many patients from his clinic and one patent can be referred from different doctors. According to your diagram, doctorid is linked with patient table and then patient table is linked with scanorders. So if you can check the relationship detail. As far as I undertand your schema represent one doctor referring many patients. I am confused.

2. Scan charges are usually fixed for each scan offered. Can change if required. Total of about 30 scans with different charges. SO I have added original scan charges in scan table. These are preadded and predefined in scan table.

3. Regarding MR No it is a unique patient ID with different format given to each new patient. PatientId PK is less flexible regarding its format. I need 08-01-001 Format if possible. yy-mm-001

Thanks again for helping.
Kindly check the relationships in the attached table and seen if they are fine, becuse making a test form to enter data was not possible. poof.

In the schema I sent, a Doctor refers a Patient for a Scan. Every Scan has a ScanType. New ScanTypes can be added.
Patients don't arrive for a Scan without a Doctor's referral.

A different Doctor could refer the same Patient for a Scan of any ScanType.

The Patient could be referred by different Doctors and have many Scans of various ScanTypes. But any specific Scan would have 1 Patient referred by 1 Doctor and involve 1 ScanType.

You may wish to view the info at this link for more about relational databases.
http://r937.com/relational.html

Good luck.

 

[faisals]

Thanks for info,
I will be reading that.
Have you checked my file. Do you think the relationships are fine or do I have to implement yours. Please advise as I am stuck with this relationship stuff.

 

[jdraw]

Thanks for info,
I will be reading that.
Have you checked my file. Do you think the relationships are fine or do I have to implement yours. Please advise as I am stuck with this relationship stuff.

It really isn't a question of using the schema I suggested, or the one you have. It is getting a schema that can answer all the questions/situations that make up your business. Creating a database is not that difficult; creating a database that supports your business, that is expandable, flexible and efficient is.

Create a database according to your schema,put some sample, meaningful data into your database and see if it answers the questions you have.
What do you want the database (application) to do?
Why are you building a database? What are you trying to automate?
Do you have a list of things the database must do? Do you have a list of questions you thinkwill be answered via the database?
Can you get "answers" from the database? These are the questions you may want to "test" against a schema you plan on using for your database structure.

Do you have Patients without a Doctor referral? In my schema A Doctor refers a Patient to your company for a Scan. The only Doctors associated with your business are those who have made referrals. The only Patients are those who were referred by a Doctor and who have a Scan. Everything in your business revolves around Scan.
Take a look at the reference on relational databases and see if that helps clarify your situation.

 

[faisals]

Hi,
I have at last designed a small patient database for clinic.
Kindly check the table structure / relationships and advise. Attached is the db file.

 

[jdraw]

Hi,
I have at last designed a small patient database for clinic.
Kindly check the table structure / relationships and advise. Attached is the db file.

As per previous posts, do you have a list of questions/situations that you want this database application to assist/resolve/perform?

Is there something in the structure that you have that does not meet your expectations?

You know your business, we don't.

Have you read/reviewed the relational database reference?

 

[faisals]

I have read the suggested articles. Thanks

My only concern regarding database is with orderscan table that is using two primary key fields.
Moreover is the database normalized.
Thanks again.

 

[traveendra1]

Nice dialogue. I am also interested in patients... will join the discussion...