Solved

Clients relational database set-up advice needed

Posted on 2006-06-13
10
270 Views
Last Modified: 2010-04-27
I wish to set up a "client" relational database with fields firstname, lastname, address, one or more next-of-kin names and addresses, one or more (can be as many as 20) medical conditions.
The next-of-kin (NOK) details should be in a relational database.
The medical conditions (MC) should also be in a relational database as these are pre-defined, one line, medical condition names that could occur in one or more clients, so as far as I can see a many-to-many relationship.
I wish to be able to list those clients with a specific condition but also make a medical data-sheet that lists the client's details with medical conditions.

I have set up quite a number of relational databases but in this case I struggle with the notion that both the NOK can be one or more links to the NOK data, i.e. the client has more than one NOK and the MC can vary between one and say 20 conditions for each client.
Questions:
1. what structure should I use and what specific link fields do I need for the relations?
2. how can I use portals to enter the NOK & MC's and change/delete NOK & MC when accessing the clients data, i.e. ideally the NOK records and MC records should be entered "on the fly" when creating a client record.

Thanks, Jos.
0
Comment
Question by:jossmeele
  • 4
  • 3
  • 3
10 Comments
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
I would use 3 tables, 1 for patients, 1 for noks, 1 for mcs, all linked with the patient ID.
than you can use portals in each table to reflect what kind of view you need.
for instance, create 1 portals in the patient layout to view noks and mcs of 1 patient.
then create a portal to patients in mcs layout to view patients concerned by a given mc, etc...
0
 

Author Comment

by:jossmeele
Comment Utility
Ok, but what about the fact that one client can have a multiple of medical conditions, i.e. do I reserve the maximum no of possible fields, say 20, to link to MCs? This is what I want to avoid, I'm looking for somekind of dynamic expandable link that gives me either 1, 5 or 20 MCs (depending on the client) linked to the client record.
Also is the client-MC relationship a many-to-many? So how do I do that in FileMaker?

Thanks, Jos.
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
you don't need to reserve any field for MCs. You link the patient to the MCs table, using a 1 to many relationship; this way the amount of MCs is completely variable, has no limit, and no space is wasted.
so you need 2 tables: patient and MCs linked using the patientID.
let me know if you'd need an example, or if you'd like me to have a look ar your files
0
 

Author Comment

by:jossmeele
Comment Utility
Sorry but I'm lost at your suggestion, this is what I need:
firstname
lastname
address
medical condition1------->links to MC-record, say #12
medical condition2------->links to MC record, say #34
medical condition3------->links to MC record, say #3
etc. there could be only ONE MC or as many as 20, each a unique condition.
another client with one medical condition the same would look like this
firstname
lastname
address
medical condition1------>links to MC record#34 (same as for client above)

So please provide me with a sample of what you have in mind.

Jos.
0
 
LVL 19

Expert Comment

by:billmercer
Comment Utility
>another client with one medical condition the same would look like this
>firstname
>lastname
>address
>medical condition1------>links to MC record#34 (same as for client above)
You're describing a many-to-many relationship. This is a bit more complicated to set up.

You'll have multiple patients, and multiple medical conditions. Each patient can have more than one condition, and each condition can be associated with more than one patient.
So you'll need three tables to handle this:
A Patients table  which holds the patient's name, address, etc, and has a unique primary key.
A Conditions table which holds all the possible medical conditions, including CPT codes or something, and its own unique primary key.
A Patient-to-Condition Link table with its own primary key, and two foreign key fields, one for the patient key, and one for the condition key. This would also have any additional fields you need to describe this patient's specific case, such as the date the diagnosis was made, doctor's comments, etc.

Create a relationship between the patients table and the links table, and another relationship between the links table and the conditions table.

Suppose you have Bob Smith, who is patient number 6, and they get diagnosed with Bloaty Head Disease, which is condition number 15. You create a record in the links table and put 6 in the patient number field, and 15 in the condition field. Now when you look at Bob smiths' records, you can refer to his conditions through the relationship.

You don't need to manually enter these IDs, this can be done automatically for you by using portals and value lists.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 19

Expert Comment

by:billmercer
Comment Utility
Here's a simple example showing how a many-to-many relationship works with portals.
http://www.milleniumhandandshrimp.com/upload/ZooFood.fp7
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
Though I haven't checked Bill's example, let me know if this is enough to avoid me to waste my time redesigning the wheel.
0
 

Author Comment

by:jossmeele
Comment Utility
To: lesouef
Yep, the example from Bill gets me on the way! Thanks for your help!! Jos.

To: Bill

Thanks Bill, the example really helps, especially with setting up the portals.
One more question: Is there any way I can prevent having the maximum imaginable no of links for medical conditions in the client's table but have a dynamically expanding set instead.
Example: patient1 has 3 medical conditions, which is 3 links. patient2 has 6 conditions which means 6 links. If 6 was the max imaginable no of conditions I would have to reserve/set up 6 link fields in the primary patient record,i.e patient1 has 3 unused links in its record and patient2 uses all 6 links, correct???
0
 
LVL 19

Accepted Solution

by:
billmercer earned 250 total points
Comment Utility
>Is there any way I can prevent having the maximum imaginable no of links for medical conditions in the client's table but have a
>dynamically expanding set instead.

Actually, the example I provided does this already. Give it a try. Add some new animals and foods, and then try assigning foods to each animal. You can assign as many foods as you want to each animal, and vice versa. If there are more records than can fit in the portal, a scroll bar will appear in the portal so you can scroll down to see more possibilities.

> If 6 was the max imaginable no of conditions I would have to reserve/set up 6 link fields
Not necessary. This is the big advantage of a many-to-many relationship, you can have any number of patients, any number of conditions, and any number of links, and you don't need to put any information about the conditions in the patient's record at all.

The patient's ID will let you find all of the link records for that patient, and each link record will then point you to a condition record. If there isn't a link record, that means the patient doesn't have that condition. This is much more efficient, because you don't have to maintain a bunch of fields that are mostly empty for most patients, just because one patient happens to have lots of conditions.

There's another advantage to this design, security purposes. With medical records, confidentiality is important. People who don't NEED to know about the medical history shouldn't have access to it. So for example, if you had an employee whose job was just to mail appointment reminders to the patients, that employee would never need to know what conditoins people had. They can be given access to the patients table, but denied access to the links table. This protects you from accidentally revealing confidential information to someone who doesn't need it. And if you decide to export your patient list to an Excel file, it's more difficult to accidentally include confidential information if it's not stored in the same table.

What you want to do is design your database so that each table only contains information about the thing it represents. So the patient table should only have general information about the patient, such as name, contact information, date of birth, medical record number, etc.
The Conditions table should have any information about the various conditions in general, such as the name of the condition, the med rec code for that condition, alternate names for the same thing, or even a link to a description of the condition on a web site.

The links table should have information that is specific to THAT person's particular case of THAT condition. Such as when they were diagnosed, how severe it is, prognosis, etc.

Once this is set up, you can create an unlimited number of records. You could have a patient with thousands of conditions if you wanted to.



0
 

Author Comment

by:jossmeele
Comment Utility
Thank you, I think that resolves my question!
Jos.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now