Hi everyone,

I am not completely new to Filemaker, although I am not an expert. I
am using FM 8.5 advanced - FM 8 server on Windows XP. I am trying to
figure out the most appropriate relationship design for a clinical DB.
I will have to put basic demographic data about the patients (name,
gender, date of birth, age, first evaluation...) and then relate this
information to diagnosis and treatment. Obviously most of patients
will have a follow-up, with many records regarding diagnostic
examinations and possibly different treatments by time. I thought to
create a single table regarding patients demographics, different
tables for different diagnostic tests (like ECG, biomakers, Lab,
Imaging and so on...) and another table for treatment (correct ?).
Each patient would have a single ID and this could be the key for the
relations through all the tables. But what is the most efficient way
to relate patient informations (demographics) to more than one record
on the diagnostic/treatment tables ? (Is this a one to many
relationship ?).

Thank you in advance for any help

Diego

Diego B <messadua (AT) yahoo (DOT) it> wrote:

Hospital DBs usually have a patients table and a case table. It is 1:n
indeed. Diagnoistics and Treamtents are fields in the case table or have
their own tables and are conected to the cases with cross tables.
--
http://clk.ch

In article <1177257720.990492.192440 (AT) b58g2000hsg (DOT) googlegroups.com>,
Diego B <messadua (AT) yahoo (DOT) it> wrote:

As you say, one table for patient demographics, with records identified
by a unique number kpPatienID. I recommend using a serial number for
kpPatientID; define the field to be automatically set to contain a
serial number.

One or more tables for treatment, diagnosis, etc (could perhaps be one
table with fields for those items). One of the fields in the treatment
table would be kfPatientID.

Treatment table related to Patient table by Patient ID, thus:

Patient::kpPatientID = Treatment::kfPatientID

Define the relationship to allow creation of Treatment records from the
Patient record.

You can put a portal of the Treatment table in a layout of the Patient
table, then create Treatment records in the portal. When you do this,
the value of Patient::kPatientID is automatically filled in the field
Treatment::kfPatientID, and the relationship is made.

--
For email, change <fake> to <earthlink>
Bill Collins

On Apr 22, 2:15 pm, c... (AT) freesurf (DOT) ch (Christoph Kaufmann) wrote:
I like this idea, but how exactly should I build the cross table ?
Might you provide a practical example ?

Thanks a lot !

Diego

Diego B <messadua (AT) yahoo (DOT) it> wrote:

As in all relational databases, you need to know for every table what a
record is supposed to show. In this case, we have:

Patients or Persons: one record is one person, i.e. there are never two
or more records for the same person.

Cases: one record is a medical issue of one person. The cases have a
number field PersonSNR that links them to the persons. A person can have
no case, one case or more cases (i.e. the relation persons-cases is
1:n).

Diagnosis: every record is a possible diagnosis. Just diagnoses as you
find them in the book, not the diagnoses of a certain patient.

We want to relate cases to diagnoses, but there's a problem: We can have
more than one diagnosis in a case. OTOH, one diagnose will be used in
several cases. The relation is not 1:n or n:1, but n:m. Therefore we
cannot link the tables directly. In this case, we use a cross table:

CrossCasesDiagnoses: every record is one diagnosis in one case. Mark
this definition. You need two fields only: CaseSNR and DiagnosisSNR.

Now you make the relations:

Cases - CrossCasesDiagnoses using CaseSNR
CrossCasesDiagnoses - Diagnoses using DiagnosisSNR

If case 3 has the diagnoseses 10, 11, 27 and 34, you have to create 4
records in the cross table:

CaseSNR DiagnosSNR
3 10
3 11
3 27
3 34

Good luck!

The easiest way to create such record is probably in a portal in the
table cases. The portal is based on the relation
cases-CrossCasesDiagnoses.
--
http://clk.ch