Link to home
Start Free TrialLog in
Avatar of jossmeele
jossmeeleFlag for United States of America

asked on

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.
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.
Avatar of Member_2_908359
Member_2_908359
Flag of France image

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...
Avatar of jossmeele

ASKER

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.
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
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.
Avatar of billmercer
billmercer

>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.
Here's a simple example showing how a many-to-many relationship works with portals.
http://www.milleniumhandandshrimp.com/upload/ZooFood.fp7
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.
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???
ASKER CERTIFIED SOLUTION
Avatar of billmercer
billmercer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you, I think that resolves my question!
Jos.