2 Replies Latest reply on Jan 3, 2014 5:34 PM by LyndsayHowarth

    Problem determining what tables are needed in a database

    jamesonelam

      Hi all,

      Pretty wet behind the ears with all of this but I have done my best to watch as many instructional vids and complete real database exercises in order to learn. So here goes.

       

      I work for a company that has both field and office personnel. I am trying to build a database that will track inventory items checked out to either a specific location(field vehicle or office area/desk), or to a specific employee. I would like to show who checked out the item even if it goes to a field vehicle. It will need to keep a current record of items in the warehouse(stored). It will need to store where items come from originally(vendor/distributor). Some items are long use(flashlight, hand drill), others are general use items(printer toner, wood putty). I would like to pull reports as to where certain items currently are, or how many have been checked out to a location/employee during a specific date range. I don't necessarily need to input $value of said items at this time, but it will be easy enough later on.

       

      The list as far as I can tell of said tables should be; employees, locations, vendors and items. A join table should exist between employees and items (transactions).

       

      The main questions I have are:

      1) Can the current transactions table also be a join table between vendors and locations seeing as how one of my locations will be the warehouse or should it be a separate table occurrence of transactions linking the two?

      2) How would I link the employees and locations tables knowing that I still need to track who "checked" out an item to a location? I should mention that employees are not always in the same vehicle.

       

      I have a good idea on how to handle the calculation fields and portals once I figure these relationships out.

       

      Thank you for any help.

       

      -Jameson

        • 1. Re: Problem determining what tables are needed in a database
          Stephen Huston

          I notice that your employee and location info are described as separate tables, but you intend to use them either interchangeably, or, possibly, as attributes/modifiers of each other.

          jamesonelam wrote, in part:

           

          The main questions I have are:

          1) Can the current transactions table also be a join table between vendors and locations seeing as how one of my locations will be the warehouse or should it be a separate table occurrence of transactions linking the two?

          2) How would I link the employees and locations tables knowing that I still need to track who "checked" out an item to a location? I should mention that employees are not always in the same vehicle.

          That tells me that the employee/location info will be most efficient is those peices of info are separate fields in the same record table.

           

          From your description so far, this is the simplest structure I picture meeting your needs:

           

          Vendor -< Items -< join table of items to employees (with location attribute for employee at this instance) >- Employees

           

          Obviously, you will need date_out/date_in info in the join table. You can then run reports on the join table for items checked out but not yet returned for current location/employee.

          1 of 1 people found this helpful
          • 2. Re: Problem determining what tables are needed in a database
            LyndsayHowarth

            A join table (or more) will be necessary but I think there are some things you need to consider...

             

            Will you have more than one flashlight? What happens when it dies or is stolen?

            Do you have an inventory where stock is received from outside the system...

            Some things will check out permanently and some will be returned... to where?

            What about when something gets checked out to a vehicle... doesn't someone do that? Isn't someone responsible?

             

            I know you have considered these things but to spell them out is to define what specific relationships should occur.

             

            Your 'join table can have many functions so it is a mistake to make the base table connect directly to the employees or items.

            Just say we call this table "Transactions". You might have Transaction_Contact as one relationship to Contacts... and Transaction_Item as a relationship to Items. Each would have an ID to make the match... with the ID_Contact and the ID_Item fields in Transactions... providing the ability to see the information from Contacts and Items from the context of Transactions as well as visa versa.

            Next... there are some status and condition things which can be determined by Finds or by setting up other relationships...eg:

            Our Flashlight must be returned... so in it's item record there must be something to reflect that... like a checkbox "must return" or such.

            Our Ream of A4 paper is not expected back so it would have that checkbox empty.

            Within Transactions you might have a field which calculates the status of an item. The item is CHECKED OUT on a DATE and TIME and as it is marked "must return" in it's item table the auto-enter status might be "CHECKED OUT". When that item is returned the Status can be marked as "RETURNED" (perhaps a check-button with a script attached) and the RETURNED DATE and RETURNED TIME are auto entered from the script. Similarly the status could auto-enter the value of CONSUMED when an item is not marked as "must return".

            THAT gives you another field for matching for relationships. Within Transactions you can set up a global field which has a popup with a valuelist attached with CHECKED OUT, RETURNED and CONSUMED as options and a SELF-relationship can be created by matching the global field to the status field in the Transactions and call it something like "Transactions by Status". Using that relationship you can view the data via a portal or gotorelatedrecords() in a sorted list to give you the summaries.

             

            For Consumables you might want to add a Quantity so that you can track the amount of paper an employee uses if you suspect they might be taking it home LOL...

             

            I hope this helps you delve into the detail a little better...

             

            - Lyndsay