5 Replies Latest reply on Apr 26, 2015 6:55 AM by dsvail

    Proper tables for the solution

    tmilas

      I’m trying to set up a solution to track daily production.  We have six products (that are built in six different workstations - one workstation for each product). There are 12 people working in this department.  I want to track hours per each product, (people log their time spent on particular product and they can work in different workstations).  Some workstation can have more than one person working simultaneously.  So I need to track quantity of each product per day, who made it and time spent on it (with ability to make reports and graphs (weekly and monthly).  I want to see a list of products made by a person(s) and daily production.  My problem is the proper setup of tables (ERD)… any ideas? I tried to setup a “Date” as a primary table—that didn't work too well.  Any help would be greatly appreciated.

      Tom

        • 1. Re: Proper tables for the solution
          siplus

          I always begin with paper and pen, drawing the interaction and the interface following from it; the tables come as a result. Anyway, reports and graphs are IMHO best done in the table where the data is, so you need for sure a “TimeSlice” table with

           

          PK_TimeSliceID

          FK_PersonID

          FK_ProductID

          FK_WorkstationID

          TimestampStart

          TimestampStop

          calc field: TimeSpent = Stop - Start

           

          the remaining tables follow from this.


          Of course this is the minimum, but it should get you started.

          • 2. Re: Proper tables for the solution
            erolst

            siplus wrote:

            the remaining tables follow from this

             

            An (additional) approach in the same vein is to start at the end: mock up the report(s) you want, with all the info you need to include; then work your way backwards and think what entities (and derived entities and relationships) you would need to store and/or calculate to get there.

            • 3. Re: Proper tables for the solution
              CarstenLevin

              It is indeed important to come up with the right ERD. But before doing so you need to have a look at the processes an on how your people are working.

               

              If you set up a model with very extensive reporting and the need for detailed in-data during the work, you will have to ask the question: Is it realistic for your employees to enter this data during the work, and how detailed information can they enter without loosing momentum. And can it provide some benefits to the people at the floor and the work process to make it worthwhile?

               

              Then, when you know this, do as erolst say: Make mockups of your reports. What data do the information you display need behind it.

               

              Business first

                   Procedures, rules, workflow

                             Keep asking: What value is the individual procedure/workflow and rule adding!

                        Then reports and layout mockups

                             Then ERD

                                  Then development

               

              And then everything suddenly become very easy and you and you avoid a lot of mistakes and your solution and your business will be better aligned!


              Also consider changing your procedures before or when building a system for it. Have a look at Michael Hammer's article here. It is not very new but ever so relevant.


              Best regards


              Carsten

              • 4. Re: Proper tables for the solution
                siplus

                I don't know how much it takes to complete a single product but if it takes more than X minutes and/or you want increased precision you might also consider adding a TimePaused field.

                 

                When a worker goes for a coffee break he should hit a pause button, which becomes resume and has to be hit again when back. You add the times resumeT - pauseT to the TimePaused field and the TimeSpent becomes Stop - Start - TimePaused.

                 

                I can't see any benefits for the workers (unless you intend to give them a bonus for making the products faster and this becomes the reason for being tracked in their mind) so the interaction with this system should be reduced to as few clicks as possible.

                • 5. Re: Proper tables for the solution
                  dsvail

                  Tom,  here is maybe a starting point ... from what you have described,  sounds like (making an assumption) that you are trying to find the cost of making each of your products.  That labor cost is one part of the overall cost of items.   One approach would be sort of TimeSheet scenario with the following tables.  Workers, Timesheet, Products, Timesheet_Items.     The Timesheet_Items table would be a join table between Timesheet and Products (like Invoice type scenario where you have an invoice with multiple line items showing multiple products sold).   Difference being, in an invoice example you are determining the total based on qty x price ... here you are trying to determine the cost per item made .... qty(hrs) / total = cost / piece.    Example ERD.png

                   

                  above was derived from assumptions I made...