8 Replies Latest reply on Feb 2, 2013 7:04 AM by JimMac

    A key question (FM Pro 10 Advanced)

    douglerner_1

      Title

      A key question (FM Pro 10 Advanced)

      Post

           Hi, people. Here I am back with a beginniner's question.

           If I want to relate two tables (e.g. a Customers table of company/organization info and a contacts table of people at those companies) I understand you link them together by key in each table using the relationships graph.

           And I also know that most people choose a neutral name for each key field, like "key" or "_key", and assign something straightforward like a unique integer to the key. And of course I understand that the uniqueness is important.

           But I was wondering, what would be the consequences of just using the company's name as a key for the company table? The reason I was thinking of abandoning something like a company number is because QuickBooks Online (believe it or not) does not have a slot for a key like company number!

           So I was thinking, do I really need it? What if I just use the company name itself?

           And herein lies my question. How clever is FM about changes to the key value? For example, if I change the spelling or name of a company is FM clever enough to automatically maintain all the relationships? Or does a key have to be fixed and eternal and never changing?

           Thanks,

           doug

        • 1. Re: A key question (FM Pro 10 Advanced)
          veramilo_1

               I'm not an expert but I can tell you that FM does NOT update the key field values for you.  I don't think keys are supposed to work that way.

               If you use names, you have to come up with the keys and make sure every single one of them is unique.  Even if you set up the field to require a unique value, it could get very frustrating if it kept you from continuing until you came up with a unique key.  (And I don't think that's what the unique value setting is for anyway.).  If you serialize, it's all automatic and always unique -- as long as you don't interfere with the sequence.  You never have to know or see (or even type) a key value for it to do it's job so it doesn't matter that it's some nondescript number.  

               You can google "best practices in db design" to maybe find a better answer to the issue of numbers instead of names.  Sometime studying something like that makes other things make more sense, too.  I know it helped me. 

          • 2. Re: A key question (FM Pro 10 Advanced)
            JimMac

                 I would generate a CompanyID number using Auto Entry sequence which would generate a Unique primary key (pk).  No duplicate Company names or typo's.  Each Contact would have the foreign key (fk) of the CompanyID they belonged to.  Thus the 'link".  For ease of posting ...

            pkCompanyID -------<fkCompanyID  called one CompanyID to many contact CompanyID  relationship.

                 Jim...

            • 3. Re: A key question (FM Pro 10 Advanced)
              douglerner_1

                   OK. Thanks, people. It looks like I have to give this some more study and thought.

                   Thanks,

                   doug

              • 4. Re: A key question (FM Pro 10 Advanced)
                douglerner_1

                     I'm re-reading my "Filemaker Pro 10 - The Missing Manual" chapter on relations and all that. It's bringing things back. I just need to get it done though. Time's slipping by and data is scattered.

                     doug

                • 5. Re: A key question (FM Pro 10 Advanced)
                  philmodjunk

                       I've been playing around with relationships and value lists that are properly user friendly, but still link records by ID numbers. Some of my experiments are bringing me to the opinion that sometimes a Name based primary key is acceptable--but only if you understand all the potential complications that ensue and are able to take steps to put in place scripts and interface design elements to handle them. What's leading me to have that opinion, is that the steps needed to manage this often aren't terribly complicated and much of those steps are needed anyway once you get beyond the basic level "id Number in column 1, Name in column 2" value list and try to use something better suited for working with larger value lists.

                       Please note the following issues, however, that such an approach has to be designed to handle in a error resistant, user friendly way.

                       1) Names are not unique--even company names. If you use names as your primary key, you need a bullet proof means to ensure that eacn record has a unique name.

                       2) Names change--both for people and companies and names get entered incorrectly and have to be changed to correct the error. If you use names for your primary key, you need to have a system in place for correctly managing a name change correctly in both the parent table and any related child tables that link to this primary key.

                       You also have to be careful to avoid "cascading update" situations where a name change requires updating large numbers of records--which can affect system performance and have record locking issues on a network hosted file. This is the most serious draw back to using names as primary keys.

                  • 6. Re: A key question (FM Pro 10 Advanced)
                    douglerner_1

                         Thanks for that. I was not so much worried about uniqueness (in the sense I was sure I could maintain that) as wondering if a primary key name changes (e.g. the company name changed, which happens sometimes) would all the relationships automatically just adjust or not.

                         But I think I'll stick with automatically generated unique identifiers for now.

                         Thanks,

                         doug

                    • 7. Re: A key question (FM Pro 10 Advanced)
                      douglerner_1

                           After re-reading some stuff in "Filemaker Pro 10 - The Missing Manual" I've become convinced that surrogate keys generated by FM itself are the way to go. It's just too important to have a unique and guaranteed unchanging value as the key to a record in a table. It's rare that a value is guaranteed to never change, and when it does everything is not automatically updated to take that into account. So I'm going to play it safe.

                           I'm getting some new customers for some new software I'll be handling. The data, even after all these years, is just incredibly disorganized. I've found cases where we seem to be actually paying the customer to use our services because the core resources cost more than they are paying us. And not because they need the resources - it's because we are just not keeping track well.

                           The problem is that everything is scattered among different Excel spreadsheets, so you have to find one key in one sheet, use that to look up info in another sheet, use that to look up info in yet another sheet ad infinitum.

                           I'm hoping if I get it all into FM in a thoughtful way it will all be easier to keep track of. Some people use Excel itself as a relational database I understand. But I think this is more suited for something intended to be a relational database, like FM.

                            

                           doug
                            

                      • 8. Re: A key question (FM Pro 10 Advanced)
                        JimMac

                             You are on the right track.  Excel is a spreadsheet, not a database program.  I like to use the name sake to think of organiaztion of data.  I wonder why they named it FileMakerenlightened.  Think of a filing system for ease of use and separate your info into those files (tables) then design the connections or crossreference (relationships).  Good luck.

                             Jim...