Wierd time punch system problem
Moderator: General Moderators
Wierd time punch system problem
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.
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.
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.
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.
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.
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?
How often would you need to fetch the data? Is it like a per-paycheck bi-weekly thing or more?
-
nickvd
- DevNet Resident
- Posts: 1027
- Joined: Thu Mar 10, 2005 5:27 pm
- Location: Southern Ontario
- Contact:
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
That query probably wont work as is (or even at all), i'm tired and typed it out fairly quickly
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:30pm6200 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.
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.