7 Replies Latest reply on Nov 5, 2013 12:40 PM by mcurley

    Conventional wisdom related to human and group elements in database design...

    jppuckett

      Simply put, in the past I would have created separate tables for the different types of contacts included in my database. In previous databases, I might end up with a table called Vendor Contacts, for contacts outside the org, and one called Users, for internal contacts. I'm trying to think through the issues I might face if I were to just make a single table of contacts and have a designation for each to be specified as a Vendor or a User. Any glaring problems come to mind if I am mixing my internal and external contacts in one table?

       

      TIA

        • 1. Re: Conventional wisdom related to human and group elements in database design...
          mikebeargie

          Filemaker (fortunately) offers a few options for limiting access based on an indicator.

           

          Assumably you will have users that are accessing internal or external (but not both) sets of contacts. You can define privilege sets for each user group, and only allow them read/edit-access to records when indicator = ____.

          • 2. Re: Conventional wisdom related to human and group elements in database design...
            jppuckett

            Thanks for the comment, Mike.  That's exactly what brings me to the question; that I could indicate the type of user by a field in the table itself rather than using separate tables.  See, in the past for example, I created a database for a small forensics firm.  Looking back on that database, I see now that I had several tables containing 'contacts', each table holding a particular type of contact.  I had a table for our in-house investigators called tblInvestigators, one for attorneys we worked with called tblAttorneys, another for other experts called tblExperts, and still another for injured individuals called tblOccupants. What I'm realizing at this point, is that I could combine all these types of "People" into one table with an indicator as to what type of entity they are (Investigator, Attorney, Expert or Occupant)...or I could continue with the separate table structure for each.  Just trying to determine if there is any overarching wisdom (or danger) in one approach over the other.

            • 3. Re: Conventional wisdom related to human and group elements in database design...
              PSI

              Hi JP,

               

              off the top of my head...

               

              Displaying the proper contacts for the proper parent table would require using a letter prefix for the primary key in the parent table. For instance

              Vendor PK would be V0001, V0002 etc. Clients PK CL0001 etc. These PK's would be the foriegn key in the contacts table. This prefix also provides a visual clue as to where that contact belongs if your looking at the raw contact data.

               

              I would also add another field to the contacts table that identifies the partent table. This could be an auto-enter calculation that parses the table prefix from the foreign key. When you need to isolate say all vendor contacts for a value list you could use a constant that matches the prefix.

               

              John Morina

              Pueblo System, Inc.

              CCQ-FM, Inc.

              • 4. Re: Conventional wisdom related to human and group elements in database design...
                mikebeargie

                The danger would lie in how you let users access the data (which you are able to constrain through privileges) or for any raw processing you do on that data set (IE replaces, deletions, reports, etc..) Having everything in one table does add one extra step (usually "constrain found set") in most filemaker output methods to make sure you're only getting the data required for the screen.

                 

                The advantage to keeping all the data in one table is the simplicity and the ability to make changes centrally (as opposed to adding a new email field to six different tables, or doing a replace to add zip+4 to six different tables).

                 

                If it's mission critical and needs to be locked down to an extremely secure/safe level, separate tables is still a viable and accepted solution. If you're looking to streamline things, then there's nothing holding you back from combining them to one.

                • 5. Re: Conventional wisdom related to human and group elements in database design...
                  AlanStirling

                  Hi JP

                   

                  I think there's one other factor to take into account if you do try to combine all six contacts tables.

                   

                             Does any one contact appear in more than one of the current tables?

                   

                  If you are going to move all contacts into one table, then you wouldn't want to have to hold and maintain duplicate records for the same person. So you might have to allow for one contact record to be linked to multiple tables in your relationship graph - and you may have to check when adding new contacts that the same details don't already exist somewhere in the contacts table. This might mean holding the new contact data being entered, in global fields, while you compare certain fields with data in the existing records. Then you can decide whether to show the matching record or add a new one.

                   

                  These are not major issues, but your design should be able to easily cope with them.

                   

                  Best wishes - Alan Stirling, London UK.

                   

                   

                  Alan Stirling Technology Ltd, 135 Lisson Grove, London NW1 6UP

                  +44 (0) 20 7724 2456 - alan@ast.fm - www.ast.fm.

                  FileMaker Certified Developer for versions 7, 8, 9, 10, 11 and 12.

                  • 6. Re: Conventional wisdom related to human and group elements in database design...
                    DavidJondreau

                    I'd recommend *more* tables. Have a People table and have a Customers table, a Vendors table, etc. Store the People-centered info (Name, Address, Birth Date, etc) in the People table and the Vendor-specifc info in the Vendor table.

                     

                    Users will probably want to access a layout just for Vendors. It will be confusing to see 500 records in the Vendors layout Status Area when there's really only 50 Vendors.

                    • 7. Re: Conventional wisdom related to human and group elements in database design...
                      mcurley

                      I like having seperate tables for streamlining the set for users, and I like a single table for searching across all records. DavidJondreau's is a good third option where all People details are kept in a single table, and seperate tables are used as vendor, customer, whatever holding a unique key to that table and the foreign key to the people table.  I find it useful to throw a full name lookup on the seperate table so I don't have to add the contact table to the relationship graph everytime I want to show a list of vendor names.