2 Replies Latest reply on Feb 5, 2010 1:38 PM by ninja

    auto-enter serial with letters: problem as a 'key'?

    k1ngf1sher

      Title

      auto-enter serial with letters: problem as a 'key'?

      Post

      Hi all, I'm working with a database with 20+ tables and need to figure out what to do for keys for each table. I've never had formal training in making databases and only learned recently that I should *never* use a edit-able field as a key (because links can be broken when the value is changed, etc.; I know, common knowledge, right?).  This leads me to consider what values I can use as each table's 'key.'  I had already used a auto-enter serial number that included two sets of letters along with the incrementing number (e.g., TCI309L), which included my firm's initials and a terminal letter which indicates the type of data (L=Livestock, M=Manure, F=Farm field, etc.).  But, I have several questions related to this situation:

      1. Should I have a unique, auto-enter serial key Field for each unique table in the database?

      2. Am I violating the FM laws of nature by mixing alphanumerics for the auto-enter serial (NB: each Field is identified as a Text field).

       

      Any thoughts/recommendations/condemnations welcome.

      K

        • 1. Re: auto-enter serial with letters: problem as a 'key'?
          philmodjunk
            

          1. Should I have a unique, auto-enter serial key Field for each unique table in the database?

          Not a bad idea and it does no harm. You'll only need such a field when linking a record in that table to child records in another table.

           

          2. Am I violating the FM laws of nature by mixing alphanumerics for the auto-enter serial (NB: each Field is identified as a Text field).

          No, but it's usually not the best approach. Any time you add "meaning" to your serial number as stored in the serial number field, you set yourself up for needing to edit that part of the number at a future point in time and that breaks your relationship.

           

          A better approach:

           

          Store your "letters" in a text field.

          Set up your serial number field as a simple auto-entered serial number.

          Link tables only via the serial number field. (Don't also include the text field--except in special use cases where the relationship is used to find data not create new records).

           

          For purposes of display, sorts, and finding records, define a calculation field like this: LetterField & SerialNumberField

           

          Place that field on your reports and data entry layouts where you need to see the "combined" serial number.

          • 2. Re: auto-enter serial with letters: problem as a 'key'?
            ninja
              

            I agree with Phil (usually do, but not always ;) ),

             

            I opt to mix letters and numbers anyway as a personal shortcut (typically prefixing my serials so I can tell them apart quickly)

            WS-000123, WS-000124 for weight sheets...  just a personal preference.

             

            This can also be handy when you have travelling copies of your dbase:

            Desktop serials start with D-, LaptopA serials start with LA-, etc.

             

            It helps you to know which records to check for updates on after a business trip.

             

            If you choose to mix letter and numbers...make ABSOLUTELY sure that your serial field is text.