3 Replies Latest reply on Jun 24, 2013 10:10 AM by philmodjunk

    Composite Key

    Svetlana

      Title

      Composite Key

      Post

           Hello everyone,


           I need help putting together a script that generates a unique composite key. The composite key will be the "Item number" that the company uses to identify each item it created, and it is a necessary component of the Product DB the company already has- as each record in the DB is based on the Item Number. The Item Number has to be based on two fields that the user has to enter before it can be generated, followed by a number sequence that is unique to each combination of the two fields. It also gets a little more tricky, however.

           Here is an example of how an item number looks for the company: WDWA0001. (It is broken down into WD- WA- 0001).

           "WD" should be returned if the value is "Disney" in the field called License."WA" should be returned if the value is "Wallet" in the field called Product. The values for both fields are currently from seperate value lists.

           To make this script work, I was thinking of creating seperate tables- one called "License" and one called "Product," with two seperate fields for the name of the license/product ("Disney") and the abbreviation ("WD"). Is that a good idea?


           I hope that made sense. Please let me know if I can clarify anything or add more info to help. Any help or guidance would be much appreciated. I've spent quite some time pouring over forum posts and any blog posts I could find, but nothing that really helps me get a clear understanding of how to structure this or where to even begin.

        • 1. Re: Composite Key
          philmodjunk
               

                    To make this script work, I was thinking of creating seperate tables- one called "License" and one called "Product," with two seperate fields for the name of the license/product ("Disney") and the abbreviation ("WD"). Is that a good idea?

               Yes, that sounds like a good idea.

          BUT, do not use this field as a primary key for linking to other tables in your database. Use something simpler and safer such as an auto-entered serial number and leave this ID code as a field in just the product table. That way, you can use the field for searches and sorts, but if the powers that be change the values in this field, it won't screw up the function of your database.

               And be very careful how you set up your system for generating the serial number portion of this Identifier field. If it is ever possible that two or more users might be generating new ID codes at the same time, there is a chance that you will get duplicate values. You need a text field that uses an auto-enter calcualtion to combine the three values in to a single code and then you can set up a unique values validation field option to ensure that you cannot get duplicate values in this field.

          • 2. Re: Composite Key
            Svetlana

                 Thank you for the reply and all the suggestions!

                 Firstly, I need some help with the calculation. If you could at least point me in the direction of a forum post that previously addressed a similar question and would have an example to help, I'd be grateful.

                 Also, there will never be more than one user generating new ID codes at the same time, so hopefully that diminshes the probability of duplicate values? However, I AM worried that an auto-enter calculation may overlook the ID codes that are already in the DB and create duplicates. Is there a way to assure that it will generate the code following a value that was in the DB before the calculation was created and implemented? For example, if there was already an ID code "WDWA0145," the next code it generates should be "WDWA0146" and so on. There are already over 12,000 such ID codes in the DB.

                 I'm not sure if you need to know this in order to help, but I'll post it just in case. Before the ID code is generated, the user will choose the License field and Product field values from a drop-down list whose values will be from a value list that displays data from the fields in their respective tables. For example, the Product Type value list display values from Product::Product Type, License Type from License::License Type, etc.

                 Thanks again for all of your help!

            • 3. Re: Composite Key
              philmodjunk
                   

                        Also, there will never be more than one user generating new ID codes at the same time,

                   Make sure that is NEVER EVER. If there is even the slightest possibility that this could happen, you need to take precautions or you can end up with chaos in in your data. The Unique Values validation method I mentioned earlier should do that.

                   With a self join relationship that matches values by just the first part of this identifier (WDWA in your latest example). Max ( SelfJoin::Serial ) + 1 can be used in an auto-enter calculation to enter the next value in the series. The number field would use an auto-enter calculation such as:

                   Max ( SelfJoin::Serial ) + 1 with the "do not replace existing value..." box cleared.

                   The full autoenter calculation looks like:

                   LicenseCode & ProductCategoryCode & Right ( "000" & Serial ; 4 )