Clients relational database set-up advice needed

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.
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.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

billmercerConnect With a Mentor Commented:
>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.

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...
jossmeeleAuthor Commented:
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.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
jossmeeleAuthor Commented:
Sorry but I'm lost at your suggestion, this is what I need:
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
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.

>another client with one medical condition the same would look like this
>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.
Here's a simple example showing how a many-to-many relationship works with portals.
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.
jossmeeleAuthor Commented:
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???
jossmeeleAuthor Commented:
Thank you, I think that resolves my question!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.