3 Replies Latest reply on Apr 7, 2017 4:53 PM by jsanders

    ONE table for ALL "Locations" or in separate tables by type?

    jsanders

      So I have a delivery database that I'm rebuilding from scratch.

       

      Currently the "Locations" table is strictly for Stops on a delivery route but I have other location (Address) fields scattered through out the solution.

       

      • Vendors table has address fields for their shop and a separate set of address fields for their RemitTo.

       

      • Customers table has address fields for their Office and their BillTo location.

       

      • Insurance certificates of vendors have address fields for the insurance company that issued the cert... etc etc etc.

       

       

      So I'm thinking I should consolidate ALL location data in to one table with a "Type" field to designate what the Location record is related to: Vendors, Customers, Insurance, Route Stops and so on.

       

      This will result in a massive amount of data living in a single table.  We currently have about 10,000 stop locations, 6000 vendors (minimum of 2 locations each) with their insurance companies and a few hundred customers.  The table will grow to over 50,000 records within a year or so.

       

      So I worry about performance to some degree but the issue I can't quantify is that it just seems weird to me put Route Stop location data in the same table and the remit to address of a vendor!

       

       

      Filemaker Hive Mind: Offer up your sage advise!!

        • 1. Re: ONE table for ALL "Locations" or in separate tables by type?
          philmodjunk

          50,000 records isn't all that large.

           

          Normalizing your data is, (what we DB geeks call what you want to do here), is in general a good thing for a relational database. It standardizes how you handle this data and avoids any possible duplication of the data. (Say a customer is also a vendor...)

          1 of 1 people found this helpful
          • 2. Re: ONE table for ALL "Locations" or in separate tables by type?
            wimdecorte

            In general, a 'type' is not an entity but a qualifier of an entity.  It's an attribute of something and that would make it a field.

            1 of 1 people found this helpful
            • 3. Re: ONE table for ALL "Locations" or in separate tables by type?
              jsanders

              Thanks to everyone that contributed to my thinking process, here and in other forums.  The discussion (half of which occurred in my head) helped clarify my thinking. After digging around and asking for suggestions I've decided to take a middle road between complete data normalization and zero data normalization.

               

              I'm dealing with address or location data of 6 different types:

               

              *Employee Addresses

              *Insurance Co Addresses

              *Customer Office Addresses

              *Vendor Office Addresses

              *Factoring Company Addresses

              *Route Stop Locations

               

              Employee Addresses: This data will live in the employees table. Employees don't have multiple addresses so one set of address fields added to the "employees" table is not that big of deal. Also employee data requires a higher level of security than other location types so I'd like to keep it segregated.

               

              Route Stop Locations:  We are actively trying to guide drivers to these locations, so we have fields for: hours of operation, GPS Coordinates, IDs to related containers holding Map Images, URLs for maps and a notes field.  There is a lot data here besides just the address fields, so I'm giving this data set its own table: "stopLocations".

               

              Customer Offices:

              Vendor Offices:,

              Insurance Companies:

              Factoring Companies:

               

              These are  all standard mailing address and all these entities tend to have multiple addresses, so I'm lumping them all in a single table "addresses" with a field to flag which "Type" the address record is for.