Wierd time punch system problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ViaoV
Forum Newbie
Posts: 5
Joined: Tue Oct 03, 2006 8:36 pm

Wierd time punch system problem

Post by ViaoV »

Ok, I have kinda a weird problem. I need to store a large amount of data kinda like time clock punches. Basic punch in at this time, punch out at this time. Then I need to be able to pick a specific date/time and check to see what was "punched in" at that time.

I could store each punch in in a separate row of a table, and use MySQLs time functions to pull records between times and such. But I would rather not have to have that many rows and make it more efficient. There would more than likely be a huge amount of these "punches". But I cant think of another way to store the data and still be able to apply logic to the gaps.

Any suggestions would be awesome.
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post by waradmin »

Im tired so this may be way off, but couldnt you do single rows and have it update the cell with more information, and seperate them by a | or something then do an explode on | to seperate your values?

However, I think rows is the best way because it just makes the most sense and would be easiest to manage vs needing to try to pull a ton of information out of a single cell then split it up.
ViaoV
Forum Newbie
Posts: 5
Joined: Tue Oct 03, 2006 8:36 pm

Post by ViaoV »

True but would it be the best way to handle it when there would be say 200 "punches" per item....and hundreds or thousands of items
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post by waradmin »

Create an archiving system. Every month, or whatever have the entries that are a month+ old go into a table called archive. That way you can put your "less accessed" data there and keep your "often accessed" data in a smaller table so it loads quicker. Thats what I would do anyway, with a php script and a cron job.
ViaoV
Forum Newbie
Posts: 5
Joined: Tue Oct 03, 2006 8:36 pm

Post by ViaoV »

Im wondering if even that would be fast enough. I dont know alot about mysql benchmarking on huge databases, but would it work with maybe 50-200 "punches" per item per day. and maybe 10-20+ items a day
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post by waradmin »

With 200 rows a day max, that would be 6200 rows. However, I assume that each row would have a punch_in and punch_out making it 3100 rows. Now that is still a lot, however being as your not storing a huge ammount of data, it may not be too bad.

How often would you need to fetch the data? Is it like a per-paycheck bi-weekly thing or more?
ViaoV
Forum Newbie
Posts: 5
Joined: Tue Oct 03, 2006 8:36 pm

Post by ViaoV »

Ok if the information needed to be accessed say 10 times a day and I could dump the punch records entirely....

Do you think it would still be fairly swift and managable?
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post by waradmin »

Yeah, I think that it could be managed without an archive system. If you have 10-20+ tables, with 200 punches in each one that would be 100 rows per table with 2000 rows total, which should be just fine.
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post by nickvd »

I'll admit that i didnt read the whole thread, but the first thing that popped into mind would be to have an "item" table, without any punch in/out data, and a second punch table, with 4 fields (id, itemid, punchin, punchout), then you can use the various join methods to pull the type of data you want

Code: Select all

SELECT 
   punchtable.*, itemtable.* 
FROM
   punchtable,itemtable
LEFT JOIN 
   itemtable 
ON
   itemtable.id = punchtable.itemid
WHERE
   punchtable.punchin > 9am and punchtable.punchout < 5:30pm
That query probably wont work as is (or even at all), i'm tired and typed it out fairly quickly
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

6200 rows per day is WAY inside what MySQL can handle. I have tables with 800K+ rows and it handles it fine. You shouldn't have a problem if you rotate monthly, or periodically rotate out rows that are over 30 days old.

As for the DB format, you should definitely have 2 tables - 1 that stores the data about the item, and one that stores the punching - 1 row per in/out pair.


This project is totally within the grasp of MySQL.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
ViaoV
Forum Newbie
Posts: 5
Joined: Tue Oct 03, 2006 8:36 pm

Post by ViaoV »

Thanks guys, Im going to implement it then. Ill just rotate the tables or maybe even trash tables that get old.. Thanks alot for the help
Post Reply