Q:

Database design for files and related information

My program is analyzing big XML files and producing a report based on the analysis. XML files can be several megabytes.
After the analysis, the file and the results of the analysis are stored in a database. Currently one table with several columns for different kind of information like username, errors found in file, notices found on file, type of the file etc. and one column for the XML itself is used.
When I need to show history of these analyzes it takes a long time to iterate through all these reports. 
Should I move the XML data to an own table and just have a link to it from the original result table or what would be the correct way to make the iteration of analyzing history faster. The actual XML is not shown in the history unless user clicks a link to actually see it.
Thanks in advance.
-JPH

A:

Yes, create a separate table XmlContent which you link to. This is of course assuming that you really need to have the XML in the database itself. If you are never going to modify the source anyway you could just create a file with a uniquely generated filename on harddisk and use a string column xmlsourcepath to link to the file. This might however have a negative impact on scalability.

