Database – S3 image urls in database

I don't know enough about your system but you need to look at the following:

1-How you obtain the data and in what format? Answering this will give options of how to store it and load it initially if you will end-up using a database.

2-How do you process this raw data? Answering this, will help you figure the 'active' set size. This will help in deciding how to store and how to load the data also. You may find that you don't need the entire input record and all you need is few fields of it only. If most of the fields are not used, you can keep them in a separate archived storage.

3-How do you inquire this data (online/batch and what criteria is most likely to be used)? Answering this will be the key factor in answering how to store the data what parts to keep on-line and what parts to keep off-line. Oracle for example allows you to run SQL on text files without loading the files in first. This could be a huge time saver, but of course it depends on your scenario.

as per your point:

single individuals variant data could feasibly some day require a million rows of data in a table

I really don't understand how this is possible. If it is accurate, I am not sure how it will be used. Maybe you need to separate the concepts of mere data storage from the concept of which parts of the data will be used. If you understand more about how the data will be used, you may be able to cut down the number of rows by aggregation or a similar technique.

In short much analysis is required for before a solution can be found. The guiding principles are:

1-Know your data well

2-Cut down on row size by keeping the needed columns only and linking to off-line storage when possible

3-Cut down on total row numbers by aggregation when possible

4-Use table partitioning and avoid excessive indexing

5-Know how the users need to use this data

6-Consider loading data as it arrives

7-You are probably going to need a star schema (fact and dimensions) to speed queries, but we can't tell by just the information provided

Storing files in memory doesn't seem such a bad idea if there are only a few of them. 1Mb of data isn't very much on modern servers so it's all down to the level of simultaneous users you have and what happens when you 'run out' of memory.

In my experience I try to reduce the memory footprint size of each web service request because the server is usually a better judge of caching and allocating memory between processes and it's sometimes difficult to judge or even test how an application scales when the number of users increase and the memory pool is reducing rapidly as a result. If you go down this route then I'd recommend very careful load testing if memory is even slightly under stress.

Also, if you keep the pre-checked file in memory only then debugging/auditing the upload/edit/save cycle becomes harder because you have no record of what the file looked like before it was reviewed and saved so if you have any issue with the editing code long term (or a user even disputes what your editing code does) you can't trace back and look at the code's effect, even for a short period of hours or days. In memory processing needs more effort with debug/audit tools creation.

Alternatively, two possible suggestions:

1. Have you considered running a second database instance with staging/temporary working tables in that database only? This would keep your main database 'clean' and (possibly expensive) deletion and post-deletion optimisation functions off your main database too. You could also store a 'purge' time on the table so a scheduled job then deletes old tables in case of a no-show in terms of a user never clicking the 'save' button. (They'd be prompted to re-upload their file if they left the gap between upload and save beyond a sensible threshold).

2. If you're not expecting too many concurrent users of this functionality then you could also consider using an in memory cache (such as memcached) to store the file whilst it is being reviewed etc. A 1Gb memcached would store up to a thousand of your files simultaneously so unless you were under very heavy load, this would save you having to prompt for a re-upload from the user because the cache had flushed out your file. Note that this solution doesn't necessary preclude a good audit trail, if you choose to audit then you can turn on a simple script that reads memcached entries and writes them to disk for later audit without affecting live code.