Time keeping in DB

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

Re: Time keeping in DB

On Wed, 2009-08-05 at 14:18 -0500, Shawn McKenzie 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)
>
> --
> Thanks!
> -Shawn
> http://www.spidean.com
>
I'd go with a record per timesheet, so you might end up with more than
one timesheet per day. That way, it's just simple SQL to find out how
many hours you've worked on one day, or on one job, etc.

Thanks,
Ash
http://www.ashleysheridan.co.uk


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Time keeping in DB

--001636457ed024d4ec04706a058b
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

You don't mention what DB you're using, but mySQL can be quite a pain when
dealing with multiple time zones. Not impossible, but a hassle none the
less. Be sure to set aside a place to store this (and another spot for user
preferences to keep track of their TZ).
Jerry Wilborn
jerrywilborn@gmail.com


On Wed, Aug 5, 2009 at 2:20 PM, Ashley Sheridan wrote:

> On Wed, 2009-08-05 at 14:18 -0500, Shawn McKenzie 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)
> >
> > --
> > Thanks!
> > -Shawn
> > http://www.spidean.com
> >
> I'd go with a record per timesheet, so you might end up with more than
> one timesheet per day. That way, it's just simple SQL to find out how
> many hours you've worked on one day, or on one job, etc.
>
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--001636457ed024d4ec04706a058b--

Re: Time keeping in DB

---- Shawn McKenzie 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)
>
> --
> Thanks!
> -Shawn
> http://www.spidean.com

Depends on what you are looking to do..

Are you also needing to keep whether or not a specific project?
If it is regular time/Overtime?

It may be easier to set the database up: user,week,day,project,hours,type

Then you can query the info/user off that, it should allow you to expand as needed.

HTH,
Wolf

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Time keeping in DB

On Wed, 05 Aug 2009 22:18:31 +0300, Shawn McKenzie
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

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 and 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" wrote in message
news:5E.47.03459.7EAD97A4@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

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 and 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

Re: Time keeping in DB

off course, but this is a different subject, means a finished, not changing
time sheet is to store. in that case I would prefere to use a single record
for each sheet holding an id, possibly a date and then a medium text holding
a simple xml notation of the sheet. the benetit of that concept is again
that the timesheet can have unlimited start stop times. here again is the
final rendering of that xml data responsible for the presentation. however
the input AND output application would be a bit more complicated.


"Ben Dunlap" wrote in message
news:91.81.14714.34FF97A4@pb1.pair.com...
> > sorry man, but a good data design keeps only data in a table u can not
> > calculate. in ur case that would be only and 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

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 and 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

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 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?
>

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

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


"Shawn McKenzie" wrote in message
news:E9.66.14714.5B80A7A4@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 and 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

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
>
>
> "Shawn McKenzie" wrote in message
> news:E9.66.14714.5B80A7A4@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 and 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

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 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?
>>
>
> 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