Looking for recommendations regarding the following situation:

We are developing an attendance system for an institution using PHP and MySQL. Due to limitations in the institution's LAN, we are required to collect and store the attendance data (barcode, name, entry time, etc.) at each location where attendance is taken.

Each remote location has one or more computers with barcode readers attached. Users scan their barcode twice a day. We need to store the daily attendance results at each location for transmittal overnight to the MySQL database located at the institution's data center at 2 AM.

When the remote computers interface with the db to upload fresh attendance data, they also need to download any changes in the user table (new users, new usernames, etc.)

What do you recommend for the most efficient temporary storage of and access to the attendance data: MySQL on each remote computer or a flat db on each computer, Excel, csv file, or . . . ?

Thank you for your response and don't hesitate to ask for more clarification if needed.

if you don't need to access the data between data capture and uploading the just write it to flat files. whether you choose to do that as terse minimalist csv or human readable is up to you. a potential advantage of writing it in a human readable format is that it coudl easily become part of a formal legalistic document set if the attendance is going to be used for disciplinary purposes. if you make the directory where you write the files to be read only for approved users with only one user allowed to create files (the applications capturing the data), no users allowed to edit or delete files with te exception say of sys admin or a named useraccount then you have the beginnings of a good audit trail.

if a file goes missing in the update its still sitting on whatever device the data capture was recorded in.

heck if you wanted to you could write two flat files, one with minmialist 'terse' just the data, one formatted.

as to your batch processes that dependzs on what computers you are running the data capture on. I'd do it as a timed process (windows scheduler or cron) that pumped the data up the line well before the 02:00 deadline into a single directory that your applciationt hat stuffs the data into MySQL knows about. file naming would be important (probably could do that by creatign the file name out of the computer name, date or date and time if you coudl have multiple files from on device in any one day.

can't see why you'd need to transfer user ids back to the data capture computers, unless you feel the need to validate a card swipe. personally I'd jsut record the barcode, make certain its the right type for the application and validate on trying to pump ther data into the MySQL db. bear in mind the barcode itself should have enough validation around it to ensure the correct value is scanned.

as to how your MySQL process runs thats up to you. but I fit were me I'd accept all data into a holding table in the db. then draw valid data from the holding table into the system proper leaving the unvaildated data in that holding tank for manual processing, or split the incoming data into valid (straight to system), and invalid straight to holding table. build in some form of audit trail withint he db so that you know what happened to a specific record so it can be traced back to a specific batch.. that means not deleting data. again if this data is used for legalistic purposes you need to think about the security implications

Im nto certain Id want to use barcodes if this is legailstic as barcodes are easy to fake. the fact that someone wafted a barcode in front of a scanner doesn't neccessarily prove that they have attended (it could be cloned card, it could be someone impersonating the individual. RFID chips are trickier to fake.

Have a look at using replication server. If you have local copies of the MySQL databases then replication server will look after synchronization for you.

do you really want to suggest using a replication server model for datacapture of attendance records?

assuming that the OP is referring to a real world application as opposed to a piece of academic coursework........

Yes I would suggest using replication as this is exactly what it is designed to do, to synchronize data between databases. You can of course run jobs that extract records and synchronize the data through a set of jobs but replication handles all of this for you.

thank you for your recommendations