1 2 3 4 Previous Next 46 Replies Latest reply on Sep 13, 2012 11:10 AM by jormond

    500 fields in a table?

    redzwanlatif

      Hi. I'm a new developer and I'm working on my first custom project. I got a table which have 500 fields in it. Some of my Filemaker friends told me that it's not good to have 500 fields in one table. Some people told me to store these as 500 records in a single table instead of 500 fields in a single table. But If I follow the 500 records method, I wont be able to build a graphical periodontal chart (refer to the attachment) like this. And of course, the users wants it to be fast. So I thought of building the periodontal chart in other .fmp12 file and link it to the original db. But after I added the external FM data. All the 500 fields went empty and I need to set all the 500 fields again. Any other recommendations, methods or ideas to build this periodontal chart?

       

      Thank you

        • 1. Re: 500 fields in a table?
          gdurniak

          for use on an internal GB network, it probably won't matter

           

          it might get a bit slow, if you ever need to use it over the internet

           

          you could also try one "repeating" field for each tooth,  which would lower the field count to about 96

           

          greg

           

           

          > Some people told me to store these as 500 records in a single table

          1 of 1 people found this helpful
          • 2. Re: 500 fields in a table?
            RubenVanDenBoogaard

            Hi,

             

            500 fields in one table is no problem. It is probably not elegant, nor easy to handle, but should be no problem.

            What could be a good solution is to use repeating fields to store the different elements of the charts in.

             

            a setup could be to store the pictures of het standing teeth in a repeating field with 4 x 16 = 64 repetitions

            then store the names in a field x 64, the first field on tooth x 64, second field on tooth x 64 third field on tooth x 64

            that would store 320 fields in just 5 repeating fields.  The first tooth is in repetition 1, the second in 2 and so on.

             

            for the second an fifth row I would use a repeating field with 9 repetitions for each tooth. 288 fields -> 32

             

            That would make things much easier to maintain.

             

            Just my 2c

             

            Ruben

            • 3. Re: 500 fields in a table?
              BruceRobertson

              But If I follow the 500 records method, I wont be able to build a graphical periodontal chart (refer to the attachment) like this.

               

              Not a valid statement. Better to distinguish between what cannot be done technically and what you need to learn and describe the problem to be solved.

               

              "Right now I don't know how to build a chart like this using a 500 records method"

              • 4. Re: 500 fields in a table?
                beverly

                red, IF you had a "tooth-table" (lol not a pun on truth-table!) and put FILTERED PORTALS (one for each tooth ID/Name) on the layout, that would definitely solve the problem. I don't know how familiar you are with portals and related data.

                 

                So, where do you go from here? It depends on if you're willing to learn a different way to do this instead of 500 fields!

                Beverly

                • 5. Re: 500 fields in a table?
                  Mike_Mitchell

                  Beverly gets the pun award for the day ...   

                   

                  But yes, I agree - filtered portals would definitely be a good solution for this kind of situation.

                   

                  Mike

                  • 6. Re: 500 fields in a table?
                    comment

                    redzwanlatif wrote:

                     

                    Some of my Filemaker friends told me that it's not good to have 500 fields in one table.

                     

                    It's good to have 500 fields in one table, if you are tracking an entity that has 500 attributes. Very few things need 500 fields to describe them - in most cases, 500 fields are an indication of a flawed structure. For example, if you have numbered fields such as Tooth1, Tooth2, etc. then you are definitely on the wrong path.

                     

                    As others have noted, the way the data needs to be presented should not affect your basic data model.

                    • 7. Re: 500 fields in a table?
                      mbraendle

                      500 fields are not enough, you need much more, at least 2304(*)  ... . You show the front, the back, and the top of the teeth, but not the sides inbetween. And what is with the inner part of a tooth (e.g. if there is caries creep, e.g. beneath an inlay)? Do you use a 2D (surface) zone model, or a 3D (e.g. polyeder-triangulated) model of the teeth, or a combination of both? And what happens if the wisdom teeth are removed?

                       

                      Let's put joking aside.

                       

                      Neither your 500-field nor the suggested filtered-portal methods are viable in my opinion. Both involve data overkill, since you need to reserve database space either for 500 fields or 500 prepopulated portal records (that must be preset with either 0 = sane or  >0 = type of filling material) per denture.

                       

                      What you want to store, are only the zones that are affected. Which requires some type of coordinate system to address them.

                       

                      Which also means that you have to withdraw from the idea to visualize the teeth with FileMaker on-board means only, especially if you not only want to display the fills, but also to graphically input them. You have to decouple the tooth GUI from the database. You can do this using e.g. Flash or Java applet or a HTML5/Javascript canvas that is displayed in a WebViewer and which does the interaction with your data (e.g. data transfer forth and back, transformation of the zone coordinates to your visual representation and vice versa, user interaction).

                       

                      *(5 sides * 9 zones  + 3*3*3 cube-triangulated 3D-teeth parts) * 32 teeth (roots not included)

                      • 8. Re: 500 fields in a table?
                        comment

                        MartinBraendle wrote:

                         

                        you have to withdraw from the idea to visualize the teeth with FileMaker on-board means only

                         

                        I see nothing in the original post to justify such conclusion. You seem to know much more about this than the original post reveals.

                        • 9. Re: 500 fields in a table?
                          Hans

                          In our standardsolution we have 7500, 5800 and 3900 fields in the 3 largest tables. In all tables (+100) we have more than 40.000 fielddefinitions.

                           

                          So if you need more than 500 fields there should be no problems  ;-)

                           

                          Just go for it

                           

                          Hans

                          1 of 1 people found this helpful
                          • 10. Re: 500 fields in a table?
                            CarstenLevin

                            Well, well, well,

                             

                            One answer and one question

                             

                            1. 500 fields in a FileMaker table is not a problem. And as some has correctly pointed out, you can have as many as you want.
                            2. When performing FileMaker training courses and teaching data-modeling and the understanding of relational models we are going through intensive analysis of real world scenarios to make sure our students understand the difference between attributes and entities. Så now to the most important answer: Are you sure that you need 500 attributes (fields) witin this table for each patient? Will it be OK to discuss different models for addressing your teeth? ... of course such models should still enable you to display your graphical periodontal chart. 

                             

                            Moving Target?

                            In other cases (not your teeth example), If you need 500 attributes to an entity one questions is: Are you shooting at a moving target or are you mistaking attributes for entities?

                            How often will you need to add new attributes (fields) to the table?

                                 Is it a fixed set of attributes (like colour, weight, length, height) or will you add new all the time?

                            Does most of your records need values in most of the fields?

                             

                            I am convinced that you will get some interesting advices here if we can continue this discusion. Maybe even suggestions for a dynamic model where you will not have to create have all the attributes created for each patient's teeth, but only those few (or many) where you want to register an anomali.

                             

                            General advice

                            When reviewing existing database solutions or when performing FileMaker Training often giving this advice: If your table is containing many fields, you should consider your model. Most tables should probably have as few as between 3 and 25 fields (plus all our standard utility fields).

                            But of course there will be cases where there are really 500 meaningfull individual attributes that must be represented by each their field. In reality they are just a little bit rare.

                             

                            Some thoughts about your case

                            What about having this structure:

                            Patient-<anomaly>-tooth

                             

                            For the patient you would store general personal information.

                            The anomaly table has one record for each specific issut you want to register for this patient. For each anomaly you will relate to the specific tooth in the tooth table. You will also add atributes for where/what and maybe whether it is a primary or a permanent tooth etc. etc. Pictures of this specific tooth should be stored here (maybe a related pict. table to be able to store more pictures per tooth)

                            The tooth table goes only have one record for each possible tooth in a human mouth. Probably 32 or two sets (one with 20 primary teeth and one with 32 permant teeth?) or can some people have a few extras? It will store the general picture/graphic depiction of this tooth etc. etc.

                             

                            Now you will get down to very few fields and maximum flexibility.

                             

                            And long term you will be able to store statistics about problems with specific teeth across many patients etc. etc.

                             

                            Just for the record, FileMaker 12:

                            Number of tables per file:1 million
                            Number of records per table:.64 quadrillion total records over life time of file.
                            Maximum record size:Limited by disk space or maximum file size
                            Number of fields/columns per record:256 million total fields over life time of file.
                            Number of relationships per file:Limited only by disk space or maximum file size.
                            • 11. Re: 500 fields in a table?
                              gdurniak

                              it seems the short answer is yes, you can have thousands of fields ( like the good old "flat file" days )

                               

                              the long answer is always look for clever ways to use less fields, and more related records

                               

                              it is interesting that the Tech Spec is only concerned with the total number of Field ID's available for use "under the hood", and that the "total" includes previously deleted fields.

                               

                              greg

                               

                               

                              > Number of fields/columns per record:256 million total fields over life time of file.

                              • 12. Re: 500 fields in a table?
                                Hans

                                Just to ad some extra information to my previous post:

                                 

                                In previous versions of FileMaker (I didn't test this in FileMaker 12) there's a maximum of how many fields you can put on a layout. We actually reached that maximum on a layout with a lot of settings. I don't know exactly how many fields there were on the layout but about 1000-1500 out of a tables 2700 fields. Most of them also had 10 repetitions shown. We just solved the problem by moving some of the settings from the original settings-layout to an extra "more settings"-layout so the problem was not that big anyway.

                                 

                                Hans

                                • 13. Re: 500 fields in a table?
                                  beverly

                                  g, it's not the number of fields. it's how the data is entered and RETRIEVED. Getting a report of usable data often is the catalyst for data entry (and storage) in a way that is more efficient.

                                   

                                  Perhaps that many fields is ok for what is needed. I'd tend to "group" a bit more by having one tooth table. You can specify which "location" and all the attributes about the tooth as fields. You can even tie a patient to the tooth and history about it, if desired. Then you can push and pull at the tooth data (again no puns intended here). But having one "mouth/jaw" table and all those fields sounds like a nightmare.

                                   

                                  I have a solution for a veterinarian and all the anatomical sites are individual RECORDS, not fields per patient!

                                   

                                  YMMV,

                                  Beverly

                                  • 14. Re: 500 fields in a table?
                                    comment

                                    Hans wrote:

                                     

                                    there's a maximum of how many fields you can put on a layout. We actually reached that maximum on a layout with a lot of settings. I don't know exactly how many fields there were on the layout but about 1000-1500 out of a tables 2700 fields. Most of them also had 10 repetitions shown.

                                     

                                    There is (in version 11) a limit of 32,768 objects per layout. I have not yet met a human that could absorb 2,700 facts at a glance. I suspect they would have to wait a long time for the screen to redraw, though.

                                     

                                    In any case, I believe OP should get their data model right before discussing possible performance issues (which are very likely to disappear once the data is normalized anyway). I am afraid I wasn't able to deduce much from that picture, and I am filled with Watson-like admiration for all of you Sherlocks that did.

                                    1 2 3 4 Previous Next