2 Replies Latest reply on Jun 9, 2011 8:15 AM by JosahMower

    DB layout help

    JosahMower

      Title

      DB layout help

      Post

      I currently have 3 tables requisition, items and location. Requisition has information about when req create, what PO is assigned etc. Location as information about the location of the equipment by tag number building what room, who has it   etc. Items have information about inventory of the equipment tag number, PO attached to, device manuf, model, s/n, p/n etc.

      The problem I am confused on is weather or not to add WarrantySerialNumberReplaced, where the equipment serial number of the unit replaced by is entered and also WarrantySerialReplacedDate to the items table. I will need to keep the original serial number to the tag so that it could be looked up later for inventory check/lookup with auditors or such things.

      How would it affect me searching my tag number or serial number?

      Should I create a Warrenty table?

      I was thinking off having it marked as removed/disposed inventory and put warranty replaced date and serial boxes like above but then  reentering the tag model device etc back into the items field to create a new line item like new equipment. I need to keep the same tag number because once the PO is closed only those items can be attacahed.

      Also should I create another table for removed Items instead of keeping them in items with a removed field and details? Then have a script move the equipment information to that table?

      Just trying to think of the correct way of creating this DB for ease of use and management.

        • 1. Re: DB layout help
          aammondd

          Im going to try to explain something and I hope it fits.

          It looks like your items table needs some new fields : disposition   disposition date, replacementitemID, and replacingItemID

          It also looks like you could benefit from a self relationship. Create a table occurance of your items table and name it replacement items, (this is simply another view of the same items table so that we can build different relationships to the table.it is NOT a new table)

          Create a relationship between the two based on information that will remain the same (PO tag location etc and replacingItem = item:itemid)

          In the relationship dialog check the box "Allow creation of records via this relationship" on the relpacement items side

          Now you can add a portal to your layout based on the replacement items table occurance.

          When you wish to replace the item you can add a row of data in  the portal (it will auto populate the fields that the relationship is based on)

          You will also need to populate the replacementitemID of the original item record once a new serial id number is created. (these should be an autoenter serial number)

          Im sure thats clear as mud  :)

           

          • 2. Re: DB layout help
            JosahMower

            Ya... Where is the bottom of this muddy hole? I think I am understand what you are saying but, the self relationship is throwing me through the hoops. Actually I think knowing what to relate and how is giving me a problem. I created a quick visio of what I currently have. I didnt know if you could give a visual to help me understand more.

            So what this will do in layout mode after the portal is when someone enters a new serial number in the replacementItemId it will create another record but not, right? Or the portal is going to take them to another screen to enter the data but it is in other self join table.

            Then I can set up a search for say serial number in tblItems serialNumber and it will look for that serial number in both self join (replacementItems) and tblItems tables.

            Will it also do the samething when I were to search for Tag and it would give me the orginal and warranty replaced serial?

            Image and video hosting by TinyPic