In a program I am writing, I have 3 different ways to store user generated data:(a) XML Files for each object (.Net Serialization)(b) SQL Database (MSSQL)(c) Massive terrifying blob of data in 1 file (I'm against this one)There is absolutely no theoretical difference in performance or file size in this (hypothetical) scenario, but which would be best to use?Is SQL overkill (Considering that there will be up to 2 million items in database), or the best option? Where does the line get drawn between BLOB, XML, and SQL? The index would either link to the filename (a GUID) or a SQL item id (a GUID), so there is not difference in search functionality. The XML is stored in multiple files and file size is not much of a concern, so you would not need to parse the entire thing to access a specific item (Just open a file stream to {GUID}.xml)Edit: I'm adding some more info to make it more clear what I'm asking.I am working on a program for a client who is in the perfume design industry. In all cases, the search index will be separate (Someone else's code that I don't dare touch), and will be indexed in memory. Random access of data isn't needed in this case, as people would be pulling up the full information for a single item. In a much more clear way of saying: If you do not need to access random data in a database, and XML is easier, is it ok to use that instead of an SQL file?Edit II: Either way, it will be installed on a large server, so, I am not concerned about the client having access to MSSQL or not having enough space for ~1KB extra per entry (If not using SQL).Edit III: I am only doing single select all queries (E.g. SELECT * FROM (DB) WHERE ID = (GUID)) Or (Xml.deserialize((GUID).xml))
How to choose data storage method for user generated data?
database design
Ultimately, it comes down to what the data will look like and how it will be used.However, if there will be 2 million items, SQL is almost certainly the best choice of those three.Text files have no random access, so if you want to access item 1,999,999, you will have to read through the entire file to get to it.  And there is no reason to roll your own storage format when this problem has been solved already.The benefit of XML serialization is ease of use: you can transfer seamlessly from variables in memory to data in storage, without having to worry about it.  However, XML files are an inefficient way to store large amounts of data.  The XML syntax itself often takes up more space than the data.  And, again, there is no random access, because it is stored in a text file.  An XML file is actually worse for random access than a flat text file, because by definition you have parse the entire file to access any part of it--unlike a text file, where you can at least access the first line quickly.  Dividing the content into multiple files helps, but then you have a multi-level access strategy where you first have to locate the correct file, then parse the file to get to the content you want.An SQL database is easy to set up and use, and if you are worried about overkill, there are lightweight versions available, like SQLlite.  Also, an object relational mapper can handle some of the complexity of managing data in the database for you, if you are worried about that.