[PHP] Re: Time keeping in DB
On Wed, 05 Aug 2009 22:18:31 +0300, Shawn McKenzie nos...@mckenzies.net wrote: So, obviously not PHP related, but I'm looking for thoughts on the best way to record time sheets in a DB. A time sheet for hours worked per day, not like a time clock where you start and stop. The two possibilities that I have thought of are (these are simplistic, of course I'll be storing references to the user, the project code etc.): 1. One record for each 7 day week (year, week_num, d1, d2, d3, d4, d5, d6, d7) where the dX field holds the hours worked 2. One record for each day (date, hours) In simpliest scenarios, it is better to use first approach. it is best in terms of simplicity, usage, space consumption. in case if you need to store extra information about hours (at which location, at which time started, etc, ), then it might be better to split it to following tbles: 1. Time sheet. All basic information about sheet: user, year, week number, etc. whatever you need. Just add unique id of this timesheet Here you can also add cached version of hours per day. 2. Day information: timesheetId, dayId(1-7), hours, a lot of extra fields for this day. Of course, this is applicable only if you have a lot of extra information for each day. If not, then use easiet approach. -- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Time keeping in DB
sorry man, but a good data design keeps only data in a table u can not calculate. in ur case that would be only date start and end time. refernces to user and project/tasks in other tables. ur time sheet is definately a job for a report. that type of design limits u to nothing. a user can start ans stop as many times he wants a day or time range. u can report any number of time bits to any number of project a day or time range Ralph Shawn McKenzie nos...@mckenzies.net wrote in message news:5e.47.03459.7ead9...@pb1.pair.com... So, obviously not PHP related, but I'm looking for thoughts on the best way to record time sheets in a DB. A time sheet for hours worked per day, not like a time clock where you start and stop. The two possibilities that I have thought of are (these are simplistic, of course I'll be storing references to the user, the project code etc.): 1. One record for each 7 day week (year, week_num, d1, d2, d3, d4, d5, d6, d7) where the dX field holds the hours worked 2. One record for each day (date, hours) -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Time keeping in DB
sorry man, but a good data design keeps only data in a table u can not calculate. in ur case that would be only date start and end time. refernces to user and project/tasks in other tables. ur time sheet is definately a job for a report. that type of design limits u to nothing. a user can start ans stop as many times he wants a day or time range. u can report any number of time bits to any number of project a day or time range I agree (unless the app just doesn't have access to the start/stop data). Ben -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Time keeping in DB
Ben Dunlap wrote: sorry man, but a good data design keeps only data in a table u can not calculate. in ur case that would be only date start and end time. refernces to user and project/tasks in other tables. ur time sheet is definately a job for a report. that type of design limits u to nothing. a user can start ans stop as many times he wants a day or time range. u can report any number of time bits to any number of project a day or time range I agree (unless the app just doesn't have access to the start/stop data). Ben OK, I think I understand most points except the start and stop time. Every time sheet I have used, SAP and several other smaller ones, I enter a weeks worth of time data like: Project Sun Mon TuesWed ThurFri Sat --- Grill steaks8 8 8 8 0 Vacation0 0 0 0 8 So why wouldn't I store the dates and the hours instead of start and stop times? -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Time keeping in DB
OK, I think I understand most points except the start and stop time. Every time sheet I have used, SAP and several other smaller ones, I enter a weeks worth of time data like: Project Sun Mon TuesWed ThurFri Sat --- Grill steaks 8 8 8 8 0 Vacation 0 0 0 0 8 So why wouldn't I store the dates and the hours instead of start and stop times? Maybe it comes down to what the users of the app prefer (or what you prefer, if you're building this app for yourself). From a user's perspective, I like start/stop data-entry better. I love that I can do this in Freshbooks, for example -- just click 'start' and then later click 'stop', 'log hours' -- and I never have to think about things like how many hours are there between 11:26am and 2:12pm? I think Ralph's point was that start/stop data is about as granular as any sort of time-keeping data gets, so if you store only start/stop data, you have ultimate flexibility in the way you can manipulate that data in your app. And it's probably a reasonable generalization that the most forward-looking database designs will store data in as simple and raw a form as possible. Or as Ralph put it, a good data design keeps only data in a table u can not calculate. With start/stop data, you could create weekly timesheets like the one above, in PHP -- and you could also figure out how many hours you log before noon, on average, etc. On the other hand, if the simplest data you enter is already the implicit result of a calculation (stop_time - start_time), you've limited the flexibility of your app from the get-go. But maybe that limitation isn't significant for the app you're building. Ben -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Time keeping in DB
as I said, the job is to store a time sheet. u came up with: 1. One record for each 7 day week (year, week_num, d1, d2, d3, d4, d5, d6, d7) where the dX field holds the hours worked 2. One record for each day (date, hours) it seems that just the first record is fine. in a data design u dont hold the same data twice. so why to use the second record? if u want to use two tables, then the d1-d7 fields should not be in that record. if u use the second record it could be that there are two records with the same date, what to do with it?, whichone is valid? date-time field are a bit complicated and it is not a good idear to do them unique, that is because internally those field are stored in databases as long unsigned integer often the passed seconds since 1982 (the birth of the ibm pc) or even miliseconds. that means there is always internally a big juggling to format the date. Ralph ralph_def...@yahoo.de Shawn McKenzie nos...@mckenzies.net wrote in message news:e9.66.14714.5b80a...@pb1.pair.com... Ben Dunlap wrote: sorry man, but a good data design keeps only data in a table u can not calculate. in ur case that would be only date start and end time. refernces to user and project/tasks in other tables. ur time sheet is definately a job for a report. that type of design limits u to nothing. a user can start ans stop as many times he wants a day or time range. u can report any number of time bits to any number of project a day or time range I agree (unless the app just doesn't have access to the start/stop data). Ben OK, I think I understand most points except the start and stop time. Every time sheet I have used, SAP and several other smaller ones, I enter a weeks worth of time data like: Project Sun Mon Tues Wed Thur Fri Sat --- Grill steaks 8 8 8 8 0 Vacation 0 0 0 0 8 So why wouldn't I store the dates and the hours instead of start and stop times? -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Time keeping in DB
Ralph Deffke wrote: as I said, the job is to store a time sheet. u came up with: 1. One record for each 7 day week (year, week_num, d1, d2, d3, d4, d5, d6, d7) where the dX field holds the hours worked 2. One record for each day (date, hours) it seems that just the first record is fine. in a data design u dont hold the same data twice. so why to use the second record? if u want to use two tables, then the d1-d7 fields should not be in that record. if u use the second record it could be that there are two records with the same date, what to do with it?, whichone is valid? date-time field are a bit complicated and it is not a good idear to do them unique, that is because internally those field are stored in databases as long unsigned integer often the passed seconds since 1982 (the birth of the ibm pc) or even miliseconds. that means there is always internally a big juggling to format the date. Ralph ralph_def...@yahoo.de Shawn McKenzie nos...@mckenzies.net wrote in message news:e9.66.14714.5b80a...@pb1.pair.com... Ben Dunlap wrote: sorry man, but a good data design keeps only data in a table u can not calculate. in ur case that would be only date start and end time. refernces to user and project/tasks in other tables. ur time sheet is definately a job for a report. that type of design limits u to nothing. a user can start ans stop as many times he wants a day or time range. u can report any number of time bits to any number of project a day or time range I agree (unless the app just doesn't have access to the start/stop data). Ben OK, I think I understand most points except the start and stop time. Every time sheet I have used, SAP and several other smaller ones, I enter a weeks worth of time data like: Project Sun Mon Tues Wed Thur Fri Sat --- Grill steaks 8 8 8 8 0 Vacation 0 0 0 0 8 So why wouldn't I store the dates and the hours instead of start and stop times? -- Thanks! -Shawn http://www.spidean.com In my original post I said those were the two options, so I would choose 1 or 2 or something else. Not both. -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Time keeping in DB
Ben Dunlap wrote: OK, I think I understand most points except the start and stop time. Every time sheet I have used, SAP and several other smaller ones, I enter a weeks worth of time data like: Project Sun Mon TuesWed ThurFri Sat --- Grill steaks 8 8 8 8 0 Vacation 0 0 0 0 8 So why wouldn't I store the dates and the hours instead of start and stop times? Maybe it comes down to what the users of the app prefer (or what you prefer, if you're building this app for yourself). From a user's perspective, I like start/stop data-entry better. I love that I can do this in Freshbooks, for example -- just click 'start' and then later click 'stop', 'log hours' -- and I never have to think about things like how many hours are there between 11:26am and 2:12pm? I think Ralph's point was that start/stop data is about as granular as any sort of time-keeping data gets, so if you store only start/stop data, you have ultimate flexibility in the way you can manipulate that data in your app. And it's probably a reasonable generalization that the most forward-looking database designs will store data in as simple and raw a form as possible. Or as Ralph put it, a good data design keeps only data in a table u can not calculate. With start/stop data, you could create weekly timesheets like the one above, in PHP -- and you could also figure out how many hours you log before noon, on average, etc. On the other hand, if the simplest data you enter is already the implicit result of a calculation (stop_time - start_time), you've limited the flexibility of your app from the get-go. But maybe that limitation isn't significant for the app you're building. Ben I see. I'm coming at this from an IT consultant perspective, where you're just like an employee, you work M-F 8 or so hours a day normally. So really your just filling it out for billing but it would normally be 8 hours M-F sometimes with vacation etc. -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
