Allowing user to save data to a file

Question

Is there a more effective way than this for implementing a user-saved-file in my application?

(I'm using C# / .Net with Sql Server)

MY GOAL:

I wish to allow users to save their created datapoints (along with some other structured data) to a "project file" with arbitrary extension.

PROPOSED METHOD:

• Store each datapoint in the database along with a FileID column
• When user saves the file, grab all the datapoints: "SELECT * ... WHERE FileID = @CurrentFileID".
• Export all of those datapoints to an XML file.
• Delete all of those datapoints from the database.
• Save the XML file as (or as part of) the project file.
• Every time user loads their project file, import the data from the XML back into the database.
• Display the datapoints from the database that have FileId = Current file ID.

ALTERNATIVE: Use Sqlite and create a separate Sqlite database file for each of user's projects?

Answer 1

The "best" answer depends on several factors. You can help yourself arrive at the best implementation for you by asking some probing questions about the use of the data.

The first question I would ask is: is there any reason that you can think of, now or in the future, for storing the data points as discrete fields in the database.

Think about this question in the context of what needs to consume those data points.

If, for example, you need to be able to pull them into a report or export only a portion of them at a time based on some criteria, then you almost certainly need to store them as discrete values in the database.

However, if the points will only ever be used in your application as a complete set and you have to disassemble and reassemble the XML every time, you may want to just store the complete XML file in the database as a blob. This will make storage, backup, and update very simple operations, but will also limit future flexibility.

I tend to lean toward the first approach (discrete fields), but you could easily start with the second approach if it is a stretch to conceive of any other use for the data and, if the need arises to have discrete fields, it should be a relatively easy exercise to convert the data in the blobs into tabled data if needed.

You can refine the answer by also asking additional questions:

• What is the magnitude of the data (hundreds, thousands, millions, billions)?

• What is the frequency of inserts and updates (x per second, minute, day, year)?

• What is the desired response time (milliseconds, seconds, doesn't matter)?

If you need to insert hundreds of thousands of points per second, you might be better off storing the data as a blob.

However, if you only need to insert a few hundred points a couple of times a day, you might be better off starting with the tabled approach.

It can get complex, but by probing how the data is used in your scenario, you should be able to arrive at a pretty good answer for your system.