Storing Old Data: SQL or XML

[paulnordstrom]

Hi,So say I have data that expires, like yesterday's records - never to be changed again, but still callable whenever you want. At a certain point you would want to move that old data off the server so you wouldnt have to query tons of data, right? If you were to say, schedule an event every night to clean out the past events, what would be best format to send it to? XML? seperate SQL logging table?Also to note - would it make better sense to create an XML file for each contact and keep appending the data to one file? I haven't used XML. Or what about a txt file?Any suggestions would be appreciated,Paul

[boen_robot]

Well, considering the fact that the traffic on the old data would probably be low, I guess even a txt file having everything is acceptable. But considering you might want to manipulate the data in the rare case it would be called, you would be better off with XML. Scince XML files are "static" files, they can be copyed as easy as an HTML page.If the records per day are large and/or it is expected that at some point, the traffic on the arhives will be large, spiltting each day into it's own XML file and keeping all of the files in an "arhive" folder sounds like the best option to me.

[paulnordstrom]

Actually, I'm positive there would be no editing of this old data. Also, these logs are going to be for each profile's activity, so I think structuring the backups based on each day wouldnt really work. But yes, I would want to call the data in a structured way:

3/23/2005 8:00:00 pm profileid did this by'x'amount to that recordid

3/23/2005 9:23:00 pm profileid did this by'x'amount to that recordid

so in XML could i query everything where the action 'to that' was done to some specific 'recordid'?Dont know if that came accross clearly...Paul

Well, considering the fact that the traffic on the old data would probably be low, I guess even a txt file having everything is acceptable. But considering you might want to manipulate the data in the rare case it would be called, you would be better off with XML. Scince XML files are "static" files, they can be copyed as easy as an HTML page.If the records per day are large and/or it is expected that at some point, the traffic on the arhives will be large, spiltting each day into it's own XML file and keeping all of the files in an "arhive" folder sounds like the best option to me.

[boen_robot]

By "manipulate" I meant "querying" data or changing it's de-facto structure.Well, if it's for profile, then I guess you may also have one XML for each profile. I mean, how long can their activity be? Don't answer that... in the worst case, you'll create a new file once every X entries though if they are divided by profile, you probably wouldn't need that. Only you and/or that other member would have to have acces, so that means the XML won't be that process overloaded.Having data stored in XML means you can query it, change it's structure and transform it into various formats easily. Arhive or not, this data is going to be used (though not altered).