Page 1 of 1
Wierd time punch system problem
Posted: Tue Oct 03, 2006 8:41 pm
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.
Posted: Tue Oct 03, 2006 9:00 pm
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.
Posted: Tue Oct 03, 2006 9:10 pm
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
Posted: Tue Oct 03, 2006 9:13 pm
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.
Posted: Tue Oct 03, 2006 9:23 pm
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
Posted: Tue Oct 03, 2006 9:28 pm
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?
Posted: Tue Oct 03, 2006 9:50 pm
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?
Posted: Tue Oct 03, 2006 10:02 pm
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.
Posted: Tue Oct 03, 2006 10:20 pm
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
Posted: Wed Oct 04, 2006 12:27 pm
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.
Posted: Wed Oct 04, 2006 2:58 pm
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