Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

This topic is 5156 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

Amateur FileMaker developer here, working on a solution for a medical reporting database. Simply, I have patients, and those patients have reports that belong to them. Now, there are numerous report types, 15-20 of them, each with different criteria (fields). My questions are these:

Do empty fields take up space in the file? Meaning, if I use ONE table to create ALL the report types, creating 400-500 fields in this table and using a tabbed interface to display the fields for each report type, will it drastically increase my file size after several thousand reports when most of those fields will be empty for each record?

The only reason I have it set up this way currently is because I have yet to find a way to display records related to the patient from many different tables (many different report types). If I set it up using different tables for each report type, can I show all the reports related to one patient in one portal from all these different tables?

Any feedback / advice is greatly appreciated, thank you.

Posted

I don't think you need to worry about the file size - esp. with only "several thousand reports". The solution you have described is legitimate and easy to implement. The other option is to have one "supertype" table for all the reports, containing all the common fields, and 15-20 "subtype" tables - but usually you don't see so many subtypes. If the reports vary so drastically, perhaps you should consider yet another approach - along the lines of:

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

Posted

You'd be much beter off learning database design basics. The empty fields won't be a size issue in themselves, but that kind of field count is pretty likely to indicate a design that ought to be normalized, with related tables of various kinds.

Posted

±25 fields per table doesn't seem excessive to me.

True. But I'm not sure how that relates. He's talking 500 fields in one big table.

Posted

Never the less is it pretty often happening a table borrows the gist from spreadsheets when beginners give the tool it's first stabs:

http://fmforums.com/forum/showtopic.php?tid/214768/post/356528/#356528

Someone needs to honk the "relations" horn eventually!

--sd

Posted (edited)

Hi Kevin,

The number of fields doesn't always indicate that your solution isn't normalized. When dealing with government, it simply indicates that they (always) require 20-30 forms just to receive payments from (or register with) them.

There is nothing wrong with keeping all the fields in one table except it is difficult for the Developer to work in field definitions (I wish we could section definitions). It is quite feasible to have 200 fields for one single report and those fields are specific to that report and wouldn't be used anywhere else in your solution. One such form for a mental health agency (CPMS) contains 200 fields in itself. Since this report really isn't needed elsewhere in the structure, a separate CPMS table simplifies things.

Either way you go is fine but my personal preference is to separate out the forms. Otherwise, all 200 forms' field names would need to begin with cpms_blabla, or use separator lines etc to assist you keeping all the fields separate for each of your forms (and scrolling through them during an export, calculation and script work etc is nightmare). There is nothing wrong with 1:1 when it comes to government. Just my opinion, of course.

UPDATE: I forgot to mention ... fields which might be used on a multitude of reports should be contained in the main Patients table (such as DOB, SSN etc). Only put fields in a specific report table if the fields are specific to that report. The report table would then hold the PatientID and main patient fields would be cross-placed onto the report for printing etc.

Edited by Guest
Posted

No you're suggesting the same thing I would do, perhaps with a small deviation from it - something in the direction of David Kachels tiered tables ... from:

http://www.foundationdbs.com/Downloads/WhitePaperForFMPNovices.pdf

But the tricky part is to deside the grouping in coherent tables, in an attempt to exhaust every combination possible. But as such wouldn't a record be created if nothing needs to get stored.

--sd

Posted

Do any of you have a better solution? It doesn't seem like you have read the original post.

No, we HAVE read the original post; and that is exactly what we are commenting on.

I'm not sure what your point is. My reply was not to you; and not about you.

We are probably roughly on the same page.

Posted

We are probably roughly on the same page.

It doesn't seem that way to me. The OP presented his dilemma very clearly: either 15-20 report tables of ±25 fields each - but then there's no way to display a list of all patient's reports, or one table with a huge amount of fields.

I don't see how your reply (to the OP) addresses this issue. Moreover, I found it extremely rude.

Posted (edited)

It doesn't seem that way to me. The OP presented his dilemma very clearly: either 15-20 report tables of ±25 fields each - but then there's no way to display a list of all patient's reports, or one table with a huge amount of fields.

I don't see how your reply (to the OP) addresses this issue. Moreover, I found it extremely rude.

Please read the original post carefully. No, the OP did NOT present an option for 15-20 tables.

That is YOUR proposal. Which I agree with.

The OP presented a need for 15-20 reports; and proposed using 500 fields in a single table to do the job. Our recommendations say roughly the same thing; it is probably going to be better to develop the foundation knowledge to use a relational design. My recommendation was the generalized version of your recommendation, with added information focusing on high counts of empty fields as not a meaningful concern in itself. You are free to perceive my comments any way you want. A more accurate way is to perceive them as helpful and straightforward. One way to help accomplish that might be to listen for a little bit to my speaking tone as seen in the PauseOnError applescript presentation:

Edited by Guest
Posted

But of course at this point we just don't know enough about the data. It may even be that - for example - three tables would do the job, with multiple report configurations.

Posted

the OP did NOT present an option for 15-20 tables.

If I set it up using different tables for each report type, can I show all the reports related to one patient in one portal from all these different tables?

You are free to perceive my comments any way you want. A more accurate way is to perceive them as helpful and straightforward.

Can I suggest something? Print out this thread and ask a friend to read it.

Posted (edited)

"Can I suggest something? Print out this thread and ask a friend to read it."

You'll do the same - right?

Edited by Guest
Posted

Ouch. You're right, sorry. So - we're still recommending the same thing.

Posted

Thanks for the feedback, everybody.

I am not quite sure how better to describe my dilemma to avoid confusion, but I think you all get the gist. I have a rudimentary (but growing) grasp of relationships. I have been able to build a database that can gather patient info, create reports, create monthly reports based on those reports, etc. Right now, I am using one large table, with 500+ fields to cover every type of report we do. Here's what I understand as of now.

Pros - of using one large table

1- easy to show all related reports in one portal on the patient page

2- keeps the relationship outline pretty simple

3- My report layout seems simple, with only one layout needed to display any type of report. The different info for each report is stored in a tabbed section in the middle of the page, and depending on the report type, I automatically go to that particular tab

Cons - of using one large table

1- Possible waste of space (if, indeed, hundreds of empty fields in every record created DO take space, which it seems you are saying it won't)

2- Scrolling through 500+ fields to find the field I am looking for, no matter how well named and organized, is a little tedious.

It seems my major beef with using one large table is the ability to display all related reports in one portal. More and more people seem to suggest it's possible to show records from multiple tables in one portal, through relationship magic (probably not very magical once you know how to do it), although I have not been able to find a way to do this. Can someone suggest a resource to help me figure this out?

Posted

More and more people seem to suggest it's possible to show records from multiple tables in one portal

That's not possible, period. All records shown in a portal come from the same table. However, you are ignoring the possibility of keeping all reports in a single table, while keeping some of their fields in separate related tables. Two such options have been suggested above.

  • 6 months later...
  • Newbies
Posted

That's not possible, period. All records shown in a portal come from the same table. However, you are ignoring the possibility of keeping all reports in a single table, while keeping some of their fields in separate related tables. Two such options have been suggested above.

Sounds like an excellent and elegant solution to an otherwise bloathed table. But how does this work for importing data?

when you want to import patient records from say, an excell file, obviously you will not get away with just importing them to one table. Since the fields are split up between multiple tables, data from each field needs to go to the corresponding table for that particular piece of data in such a manner that the data is kept consistent; data belonging to one individual record/line should stay that way after the import.

Like so:


+-------------------------------------------+       +-----------------------+  +-----------------------+

|          external data source             |  ---> |        table 1        |  |        table 2        |

+-------------------------------------------+       +-----------------------+  +-----------------------+

|id |field123 |fieldxyz |field987 |fieldabc |       |id |field123 |fieldabc |  |id |fieldxyz |field987 | 

|---+---------+---------+---------+---------|       |---+---------+---------|  |---+---------+---------|

|#1 |1DATA123 |1DATAxyz |1DATA987 |1DATAabc |       |#1 |1DATA123 |1DATAabc |  |#1 |1DATAxyz |1DATA987 |

|#2 |2DATA123 |2DATAxyz |2DATA987 |2DATAabc |       |#2 |2DATA123 |2DATAabc |  |#2 |2DATAxyz |2DATA987 |

+-------------------------------------------+       +-----------------------+  +-----------------------+





instead of just:



+-------------------------------------------+       +-------------------------------------------+

|          external data source             |  ---> |                  table                    |

+-------------------------------------------+       +-------------------------------------------+

|id |field123 |fieldxyz |field987 |fieldabc |       |id |field123 |fieldabc |fieldxyz |field987 | 

|---+---------+---------+---------+---------|       |---+---------+---------+---------+---------|

|#1 |1DATA123 |1DATAxyz |1DATA987 |1DATAabc |       |#1 |1DATA123 |1DATAabc |1DATAxyz |1DATA987 |

|#2 |2DATA123 |2DATAxyz |2DATA987 |2DATAabc |       |#2 |2DATA123 |2DATAabc |2DATAxyz |2DATA987 |

+-------------------------------------------+       +-----------------------+-------------------+

hope this illustrates my concern.

Posted

Obviously, you need to import the data twice. This can be scripted, so it's transparent to the user.

Sounds like an excellent and elegant solution to an otherwise bloathed table.

This solution is NOT meant to solve the problem of "an otherwise bloathed table". If an entity (e.g. a patient) needs numerous attributes to describe it properly, then that's how many fields the table should have.

However, if you have different types of patients, and each type has its own set of attributes, then you have the option of setting up a sub-table for each type. This allows you to address each type separately, as well as the entire patients super-type.

OTOH, there are additional implementation costs (such as having to split imported data), so this is not something to rush into.

  • Newbies
Posted

I had an inkling that would be the answer thanks for the reply.

This solution is NOT meant to solve the problem of "an otherwise bloathed table"

Seems I have chosen my words somewhat carelessly. What I meant is that by splitting up the data into supper-type and subtype tables you can avoid having a table with (possibly many) empty columns/fields.

Normally when I notice that a table would have many fields that are empty(NULL) at least half of the time because they do not apply to all data (to be) contained within the table, I think about splitting up the data in two or multiple data sets, each with their own dedicated table.

This topic is 5156 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.