1 Reply Latest reply on Mar 25, 2013 12:14 PM by philmodjunk

    Single Table Database to Multiple Tables?



      Single Table Database to Multiple Tables?



           I inherited a single table database at work that includes about 80 fields by this point.  I've been learning more about databases and how they work it seems like it might be better to have a database with multiple tables for different kinds of information.  I have two questions:

           1. Is it possible to separate out the fields into multiple tables?

           2. What are the advantages and disadvantages to doing this if it is possible?  

           Thanks a lot.  

        • 1. Re: Single Table Database to Multiple Tables?

               1) If you have FileMaker Advanced, you can build a set of tables with different subsets of the original list of fields by copy and pasting the fields from the original table. If you do not have FileMaker Advanced, you can duplicate the entire table by using Import Records with "New table" selected as the target table. After duplicating the table, you can delete the fields you do not want to have in this table.

               With either approach, care must be taken or calculation fields and some auto-enter field options that refer to fields in other tables will not work in the new table until you open up the field and manually fix the broken references.

               2) Can't really say much as you do not describe the data that is being entered into your table and what kind of smaller tables you have in mind for your new version. Keeping all the data in one table might still be the optimum design.

               One change that is often a move in the right direction is to take data that is present in a repeatinf field and replace the repeating field with a related table where each repetition becomes a separate, but related record. Import Records comes with a nice tool for importing data from a repeating field into a set of non-repeating fields with the data from each repetition being imported into a different record.