AnsweredAssumed Answered

database structure optimisation

Question asked by pattyred on Aug 11, 2018
Latest reply on Aug 12, 2018 by mattel

Hello community,


I am an health practitioners and I am not a professional programmer, I am using FM since v9.

Now in our office we are using FM 16 for storing patient data using one computer as server and 9 computers (both Mac and Win) as clients.


We designed our database many years ago when we had a much less understanding of FM programming, and during the years we expanded it adding tables and fields but the structure is basically still the same, which we can label as “not the best”: we have 1 table for patients’s data and 39 tables each for specific testing sections, for example: one for cardiovascular tests, one for ophthalmic examination, one for internal medicine, ecc. ecc.. Each table has one or more layouts.

Each testing table typically consists of following fields:

- key

- name

- date

- test1

- test2

- test3

- test4

- etc

- calculated fields for comparing data among different tests


In some tables there are up to 44 different tests plus many calculated and summary fields.


Using a unique key, each table is related to patient table, and some tables are linked to other tables for sharing appropriate information (for example Anamnesis information is shared across all testing tables) and calculated fields are used for comparing data with other values of the same table or from other tables.


Why we did that when we started ? Having all those value in the same table, or in related tables (as it is now), it is easy to have calculated fields comparing many different values, but the situation, as many of you can imagine, is that those table are quite busy with many fields, and the relationship graphic is complex.


Along the time we became aware that we could/should manage the structure in a more appropriate way.


For example, talking with colleagues, they suggest that the best structure should NOT be having test1, test2, test3, test4…. in the same table, but each test should be stored in a single record, allowing a much less number of fields.

If we understand correctly, we should have a table with tests and a table with sub-tests with very few fields and many records, something like:

- key

- name

- date ??? (not sure should be fitted in here)

- test name

- sub-test


That would be fine, but the particular aspect that is not totally clear to us is: if I have each test in separate record how do we relate those records to other records of the same test or even other tests ?

For example, blood pressure results are not based on single value but also on diastolic, systolic, heart rate, weight, eye pressure, kidney evaluation, etc.


So, here comes our question.

We need to understand the best strategy to have each single test and/or sub-test stored into a single record while maintaining the possibility of calculating each specific test result based on values stored in other records.


If anyone more experienced has the time to give me guidelines I would be very grateful.


Best regards,