11 Replies Latest reply on May 3, 2012 11:45 AM by timwhisenant

    attempt to normalize database

    ian.moree

      Currently I have a few fields that are repeating fields

       

      1 is used for

       

      Time_Start (15) &

      Time_End (15) &

      work_Hours(15)

      This displays nicely in a layout, but how would i go about normalizing hte structure< would i

      create fields like

      time_Start_1

      time_Start_2

      ,etc

      same as time_End?

      thanks,

       

      -i

        • 1. Re: attempt to normalize database
          ClayTarver

          Not sure exactly what you're tracking, but for instance:   create one table of employees and link to another table that has (in addition to the linking field), time_start, time_end, date, and c_worked hours.   You would the display the records related to the employee in a portal from the "times" table.

          • 2. Re: attempt to normalize database
            digital-carpentry

            Wouldn't it be better to just add a related table to hold these rather than creating multiple fields in this table?

            • 3. Re: attempt to normalize database
              ian.moree

              So wht you are saying is create another table called, Work Attributes

               

              Within that table,

               

              add start time, End time, Time_ID_pk, related to TimeCard::ID_fk

              so each start time & end time are related to that particular TimeCard::ID?

               

              is that what you mean?

               

              thanks,

               

              -i

              • 4. Re: attempt to normalize database
                digital-carpentry

                Yes, and you can sort the relationship to keep things in order.

                 

                This will give you a better structure, and you won't have the limit of X number of entries that you would have with a repeating field or creating multiple fields.

                • 5. Re: attempt to normalize database
                  ClayTarver

                  Almost - it's a <one> time card to <many> Work Attributes, so the relationship should be between the Time Card primary key and the Work Attributes foreign key.

                  • 6. Re: attempt to normalize database
                    ian.moree

                    thanks everyone;

                     

                    -i

                    • 7. Re: attempt to normalize database
                      ian.moree

                      ON this layout, which i have changed up, i now have a portal used to enter start time and end time via onEnter Script which sets

                      TimeID and creates a new record. THinking about using the magic key for this scenario!

                       

                      i was wondering if there was another way to do this, besides list view.

                       

                      I think that was why i chose repeating fields , but i do realize they become a pain to get data from when needed.

                       

                      thanks

                       

                       

                      -i

                      • 8. Re: attempt to normalize database
                        digital-carpentry

                        So you're running your script in the Work Attributes Table?

                         

                        If I understand what you are looking for, I think what you want to do is work from the other table and display the related records from Work Attributes in a Portal.

                        • 9. Re: attempt to normalize database
                          ian.moree

                          Ok.

                           

                          I Need for the user to be able to INPUT the Timestart / TimeEnd in the TimeCards Layout,

                          this will auto create a new ID in work_Attributes ( now HOURS Table) with related TimeCard_ID_pk

                           

                          then the data will show up as shown, see screen shot.

                           

                          Am i doing this incorrectly? i want and entry form, which i have now, instead of using repeats, i am trying to have the data normalized, by establishing

                          more relations for reporting purposes and historical data as well!

                           

                          thanks,

                           

                          -ian

                          [edit] where it has HOURS TABLE above, is actually located in the HOURS table

                          on this main form is where the related portal is placed showing that data, Just wanted to know if

                          there was / were other ways rather than a portal!

                           

                          [/edit]

                          • 10. Re: attempt to normalize database
                            digital-carpentry

                            This looks good and is working for you, I was misunderstanding what you were looking for, and thought you wanted to get away from the list view.

                            • 11. Re: attempt to normalize database
                              timwhisenant

                              Ian,

                               

                                         “i was wondering if there was another way to do this, besides list view.”

                               

                               

                               

                              Using the Header (timesheet) table as entry layout and a Time-Slip table child-detail (holding a job, client code, task) in a portal works quite well where time will be accumulated later by job, client or task. The child table thru the portal will automatically create the relationship links when new records are added. Totals for total hours per day, week can easily be added to the layout (I am a fan of the form view and this is a good application for its use). 

                               

                               

                               

                              In my previous experiences, we used a program called “time slips”( a professional time billing system ) which gave me the idea of a time slip which when grouped / collected would provide an electronic version of a time sheet. I use this technique to accumulate hours per Job as well as per employee per pay period in my production system.

                               

                               

                               

                              The header file has only minimal fields and summary fields for totals (daily & total pay period). The time-slips table does all the lifting ( start / stop times, hour calculations per day and per pay period, Job #, etc) performance on FMS 11 w/4 years of time sheets is solid. No worries.

                               

                               

                               

                              Tim