I have few client local application which will insert data into a local table called as tblTransaction where I have transactionID,transactionDetailsID,outletFromID,ou tletToID ,stockID,transactionDate,transactionTime,transacti onType and some other fields. So after some interval all this data will be copied into a central database with same name tblTrandsaction so the primary key I have set as transactionID,transactionDetailsID,outletFromID,ou tletToID and transactionType. So now the problem is in the central server I would like the order the data all by the sequence of occurences(by date time). So now I depend on the date and time. So what I forsee is the local client maybe able to change their date and time and secondly due to cmos battery they can sometimes date and time becomes slower etc. So in order to avoid date and time is there any other method to maintain the sequence at the central server of all the transaction from all the clients.
Actually I have set PRIMARY KEY(transactionID, transactionDetailsID, outletFromID, outletToID, transactionType). The outletFromID and outletToID are the host ID/ c which I already included into the primary key list.
Ok below is a sample of data.
Ok let me give one example two data one from outletID=2 and outletID=3 which will be copied over to the central database.
So in this scenario I will see that the data from outletID=3 is after from outletID=2 but in reality let say the exact date for outletID=3 is 2010-01-05 but it was change by the client pc maybe accidentally or purposely so then when we view this data order by date and time on the central server will be wrong order.
So how to maintain this scenario ?
Data from outletID=2
transactionID (5),transactionDetailsID (15),outletFromID(2),outletToID(2) and transactionType(s),transactionDate(2010-01-11),transactionTime(20:00:00)
Data from outletID=3
transactionID (6),transactionDetailsID (26),outletFromID(3),outletToID(3) and transactionType(s),transactionDate(2010-01-12),transactionTime(20:00:00)
Why query the date and time from the PC at all? Whenever you insert data into the database surely you should just use the current date and time on the server and don't bother even looking at the time on the client's PC. I must admit I don't understand your example so I can't give a more concrete example.