4 Replies Latest reply on Jul 13, 2009 2:29 PM by keycoachjohn

    Global and Foreign ID



      Global and Foreign ID


      I really thought I had a handle on this topic, but keep getting quirky results.  Much of what I’ve read pertaining to table relationship suggests creating a serialized ID on the “one” table and a non-serialized foreign ID on the “many” table; and diligently trying to avoid “many to many” relationships. 


      For simplicity I extracted the two troublesome tables just to isolate the errors (all other relationships and tunneling work perfectly)….


      Take two tables, one “Images” and one “Inventory”.  Within “Images” are 3 fields “Images ID” “Inventory Type” and a container field for a jpg.  Within “Inventory” are fields “Inventory ID”, "Image ID", “Inventory Serial Number”  “Inventory Type”.  By the way, the first table “Images” has a value list for “inventory type” that draws from table “Inventory” to ensure no input discrepancies.  Incidentally, “Inventory Type” is unique and can qualify as a ID but from a purist sense what am I overlooking? 2 IDs?  In my design, this is supposed to be a O-T-M; Images (one) Inventory (many); one image per inventory type to a whole bunch of inventory items each serialized in "Inventory".


      When I join in several combination using the ID fields I get the one-many relationship in the graph, but no jpg on the layout.  F(iguring a OTM is better than MTM.)We’re talking each direction, serializing on and off, indexing on and off, have tried about 10 variations.  No jpg.


      When I link “Inventory type” to “Inventory type” the relationship graph shows Many to Many, it works fine but have to exit FM to get updates if I add records in either of the two tables; sometimes updates don't update.  Again, playing by the rules I’m trying to avoid the M-T-M; but wonder if having two unique IDs per table is the problem and whether a MTM is a workable solution.  Thanks John.

        • 1. Re: Global and Foreign ID

          This would be a lot easier to understand if we knew what the tables and fields meant. I am pretty sure I know what an image is, but what does a record in the Inventory table represent? "One image has many inventories" just doesn't do it for me.


          And what is the difference between "Inventory ID" and “Inventory Serial Number”? And what exactly is inventory type and why does it need to be duplicated in both tables?

          • 2. Re: Global and Foreign ID

            Thanks for the quick reply; I'll try and clarify.

            A record in the inventory table represents one item in inventory, it's manufacturers serial number (like a car's VIN), a "type" description and some other information about it.  Many items in inventory, many types of inventory items.  In the "images" table, instead of having one image per each serial number, I have a general list of images relevent to the type of item in inventory...an "images" record is created to represent an image of the "inventory type".  Inventory type can be described as a set of inventory, like a model of a car; or airplane eg 737, or 757


            Inventory ID is auto generated and represents the DB global ID since it's the main table (out of 30+) that everything else relates to.  Inventory "Serial Number" is a industry type serial number that makes more sense to people reviewing the database; it's really the Manufacturers SN. 


            As to duplicating the "Inventory Type" in both?  I expect its required in "Inventory since I pull it into multiple downsteam OTM links.  As to it's need in "images" I thought it was needed to identify the "type" when someone was inputing a new "type" of inventory in "Images". 


            • 3. Re: Global and Foreign ID

              OK, then - suppose we have two tables:

              • ItemID (auto-entered serial number)
              • CategoryID

              • Description

              • ...

              • CategoryID (auto-entered serial number)
              • Category
              • Image
              • ...

              related as:

              Categories:: CategoryID = Items:: CategoryID

              Note that this assumes an item can belong to (at most) ONE category.



              Now, what exactly is your question?

              • 4. Re: Global and Foreign ID


                Working through this you've helped me recognize where the duplicity is.  I was trying to force two fields: "Type" together rather than allowing the foreign ID to help collect Type from the other table.  Made a value list on the foreign ID and it works well.  My hangup was twofold:  a) tied to the main Table and letting go of "type" as defined there, since most of the entry happens there I felt obliged (wrongly) to hold that field sacred.  b) I was reluctant to tweak on the foreign ID. 


                Now I need to make sure that other links several tables deep that refered to "Type aren't hosed "... but for now, I'll close the item.  Thanks again.