6 Replies Latest reply on Dec 31, 2016 2:17 PM by David Moyer

    When to split a table?

    cwcrogan

      I guess this question isn't specific to Filemaker but more a general database logic question.

       

      I have a database with around 10,000 records (addresses in a town). About 1,000 of which have persons to notify in an emergency attached (from 1 to 3 people). There will be nearly no duplication of PTNs. Does it make more sense to have the PTNs (and their phone(s)) as fields in the address table adding 6 or 9 (3 PTNs with 2 numbers each) extra fields or to create a new table of PTNs (people)? The former will have 9000 records with 6 or 9 empty fields which I'm not sure has any overhead or not... I'm leaning towards the creation of a second table but would like some input as to which is better and why. TIA

        • 1. Re: When to split a table?
          philmodjunk

          What advantages do you see in that additional table?

           

          I'd keep the basic info for both in one table, but would split off the phone number data into a related table so that I can link a flexible number of phone numbers to the same person. I often put email addresses in the same table using the same field for both phone and email addresses, but with an added "label" field with a value list of values such as "home", "fax", "business", "cell", "email".... to tell the user which is which.

           

          But the devil is in the details and that's not something that you've yet chosen to share in this discussion.

          • 2. Re: When to split a table?
            David Moyer

            Hi,

            a second table is ideal.  Why - even if there is little duplication, there is some duplication and should be avoided.  If a PTN gets a new phone number, it only needs to be updated in one record, no matter how many times it is referenced by an address record.  That's the main reason.  Other than that, it's standard practice.  The downside is that you need to manage the relationship and anticipate when new data entries match existing entries.

            • 3. Re: When to split a table?
              philmodjunk

              Are you sure that you really meant to say that a "second table is ideal"? The rest of your table appears to say the exact opposite.

              • 4. Re: When to split a table?
                David Moyer

                hmm,

                I see Address---<PTN

                and since people and their phone numbers are not necessarily attached to an address, I'd want to separate the two.  Maybe I simply misunderstand the problem.

                Of course, it could actually be Address>---<PTN.

                • 5. Re: When to split a table?
                  philmodjunk

                  No I think that I misunderstood you and that you have a better grasp of the issue than I did...

                  • 6. Re: When to split a table?
                    David Moyer

                    pshaw.

                    (edit ... pshaw has multiple definitions - mine wasn't disdain or contempt, just disbelief.)