5 Replies Latest reply on Oct 26, 2014 8:26 AM by philmodjunk

    Handling a bazillion fields



      Handling a bazillion fields


      In the anesthesia clinical record/billing solution I am working on, I am creating a ton of fields.  Most are tied to a specific surgery, so I am creating them in the SURGERIES table.  However, even though they will be unique to a single surgery and so (to my understanding) can reasonably all be added to that table, I find that the number is getting very large. 


      I was thinking that I could split them off into separate tables, tied to the SURGERIES record by a key field.  Thus, I could create a table called "CMS_FIELDS" that would have all of the fields related to completing the CMS claim form for the surgery, and another table called "CLINICAL RECORD" that would have all of the fields related to the clinical record for that surgery.  

      My question is:  is that the best way to do it?  Thanks.


      Dan Simonson, CRNA, MHPA
      2607 S. Manito Blvd.
      Spokane, WA 99203

      H. (509) 747-0819
      C. (509) 981-6274 



        • 1. Re: Handling a bazillion fields

          You are describing a frequently used method to keep the number of fields in a given table manageable.

          • 2. Re: Handling a bazillion fields

            Thanks for the quick answer, Phil - it sounded good to me, also - but I have been wrong before!

            • 3. Re: Handling a bazillion fields

              But what you really want to look for are ways to use multiple records to take the place of multiple fields in the same record. This is not always possible, but is the way to go with your design when it is possible.

              • 4. Re: Handling a bazillion fields

                I am not quite understanding that approach.  Is there a resource somewhere that you can point me to that goes into the idea in more depth?  Wouldn't it create difficulties trying to display multiple records as if they were one record?

                • 5. Re: Handling a bazillion fields

                  That's what portal's are for and not just as a single, table like view of multiple records as there are other options.

                  Sometimes it's an obvious thing and sometimes not so obvious. If you find you have a series of fields where the only difference is a number that's part of the field name, such as Med1, Med2, Med3... Then it's extremely likely that you can set up a portal to a related table where each Med field is replaced by a record in the related table. That's an obvious example.

                  A less obvious approach is to look for groups of fields that record the same type of data and that it makes sense to treat as a group--especially if you find that the number of fields actually used in a given record vary a lot. Here's an example of that:

                  When I set up a contact manager unit in a database, I typically define a related table for all phone numbers, email addresses and social media URL's. Instead of this set of fields:

                  Home Phone, Work Phone, Cell Phone, Email Addres, FaceBook....

                  I set up a related table with just three fields: The foreign key match field that matches back to the main record, a field for the type of info (Cell, Work, email...) and a field for the contact data (phone number, URL, etc). I not only break up my larger mass of fields into easier to manage smaller groups, but I gain flexibility as I can always add another record for this data without having to define an extra field if I get a case where the record requires more data than I anticipated with my original design.