5 Replies Latest reply on Aug 10, 2015 3:42 PM by philmodjunk

    How to have only one instance of a First Name and one instance of Last Name in a Db?

    MarioMarceau

      Title

      How to have only one instance of a First Name and one instance of Last Name in a Db?

      Post

      Hello,

      First, is the following a good idea and if so, how do I go about it?

      Take the word spelled "John". Any number of people can share that first name. Same goes for last names.

      What I'd like to do is have a list of first names, a list of last names, both with primary keys, a data entry popup that would fetch the first name and last name and when I have the information, the PKs for the first and last names would be used to create a "person".

      Say, one first name table with pk 1, FName "John", another table with LName pk 1, "Doe" and so on. The data entry popup would either write to those lists if FName and/or LName do not exist of fetch it from a drop down if they do. The person would then be created using the PKs for the Fname and LName.

      This is to avoid having several entries with the same first name and the same last name for different people (ex: John Doe, Jane Doe) in my database. In other words, one instance of "Doe" could be used to create several people with different first names.

      How do I go about doing this? In my head it seems easy but for the life of me, I can't figure out how to create such relationship. I'm stumped!

      Thank you kindly for any help,

      Mario

        • 1. Re: How to have only one instance of a First Name and one instance of Last Name in a Db?
          SteveMartino

          What kind of database is this?  Unless it's something unique where you are have a list of first names and a list of last names and are creating random records of first and last names, this sounds like a horrible idea.

          • 2. Re: How to have only one instance of a First Name and one instance of Last Name in a Db?
            MarioMarceau

             

            Hello Steve,

            While this may sound like a horrible idea (and perhaps it is), the scope of this database will be very large. Let me give you a example:

            Take Albert Einstein. A lot of people are called "Albert" and others "Einstein". Suppose I can have a (hidden) field in the data entry that will be used later to fetch from FNames list, say, PK (number) 234 from a list that contains "Albert". Another field that's similar for the Lnames say, PK 567 from a list.

            Here the thing: In my data entry field, I also provide a list field of carefully designed contexts and another for carefully chosen keywords.

            What I propose is if, later on, I search for "Albert" "Einstein". He is a scientist, he has written famous quotes and has been portrayed in several movies.

            So, In search fields, I type "Albert", "Einstein". Then I add a context, say, "Relativity". The database then searches for the PK associated with FName "Albert", the PK associated with LName "Einstein" and the PK associated with Context "Relativity" and perhaps the PK associated with Keyword "Quotes". This would fetch me a list of all the quotes written by "Albert" "Einstein" on "Relativity".

            This forces the database to look for indexed numeric PKs ONLY, base on the search query that looks for FName PKs, LName PKs and Context PKs and so on. It also forces the database to greatly narrow its search parameters. Wouldn't this eliminates redundancies and overhead of having several entries with the exact same information?

            I'm well aware that text and other information information cannot be handled in the same way. That's not a problem.

            Thank you.

            • 3. Re: How to have only one instance of a First Name and one instance of Last Name in a Db?
              SteveMartino

              FileMaker searches 100s of 1000's of records pretty quickly.  What you are proposing doesn't speed up the process at all.  FileMaker would search the name fields and return the primary keys, or a record, or a few records.  It all depends on what you want to see after you type "Albert" "Einstein" and "Relativity", and where and how you want to see it.  Do you want to land on AE's record, or a record about relativity?  If all the fields you are searching are indexed, you'll get rapid results.  You can see this if you set up a type-ahead field, and see how fast the results are attained.

              A little more about your work flow, how the DB will be set up, etc., and others could provide more guidance.

              I'm well aware that text and other information information cannot be handled in the same way.

              I don't know what that means, or how you drew that conclusion.

              • 4. Re: How to have only one instance of a First Name and one instance of Last Name in a Db?
                MarioMarceau

                Oh! Ok Steve,

                Your answer makes a lot of sense. I was trying to emulate what search engines like Google and others do to eliminate redundancies. I wanted to try different algorithms. As you put it, if FMPro can search 100s of 1000s of records very quickly, then I see no need to proceed with this wild idea (which, incidentally, will make it much easier to build my Db).

                Just to clarify, the quote you specified was just to explain that I wouldn't proceed the same way with those other fields.

                Anyhow, you did answer my question very clearly. I'll go with your good advice.

                Thank you very much Steve,

                Best regards,

                Mario

                • 5. Re: How to have only one instance of a First Name and one instance of Last Name in a Db?
                  philmodjunk

                  A suggestion:

                  If you have a first name field and a last name field, then a text field with this auto-enter calculation:

                  FirstName & "|" & LastName

                  could be given a unique values validation to ensure that no two records have the same combination of first and last names in your database.

                  But it's quite possible to allow such duplicate names as long as you use a primary key to identify records and to link to other tables in relationships. You would then only use the name for searches and sorts of your data.