One table with a flag or two tables

I have a problem with the design of a database: In my Android application i need to store the upload (the status, so if the file is in upload it will report the % of the upload) of a file and a list of all files uploaded.

First, i was design the database to something like this:

Files
----------------
_id INT PK,
status INT,
progress INT,
imageupload BLOB

So status will be the status of the upload which will be

0. Unknown
1. In upload
2. Error
3. Success

While imageupload is just a class serializable which contains: imageTitle, imagesUri (A List), album, privateImage.

(i said that in case exists another better way to design it which i didn’t know and avoid using a blob)

With this design i could see if a file is in upload by reading the status WHERE status != 3 AND status != 2

Another design which could be used is to use two tables where in one table are stored the in upload files and in another the already uploaded.

With the two tables i avoid using status but it will let me execute two queries inside to select all files and order them by IN-UPLOAD/UPLOADED.

What is the best practice? Is one table prefered over two tables? I would use one table since it’s more easy and can be edited without problems. With the second version i should move the record from in_upload table to uploaded table (which could be problematic).

If it’s important i’m using SQLite.

I would go with the single-table option. It’s simpler and I don’t see any reason why you would need two tables in this case. If you want to make it easier to find records with status of “2” or “3”, you could create a view that only displays these.

The only reason I could see for having two tables is if inserting/updating with a BLOB is very slow and causes performance hits for queries on the same table.

Maybe you don’t expect this answer, but do both. Let’s see.

• In order to accommodate for the life-cycle of the data ( something many database designers don’t contemplate ), you should have a “historic” table with the downloads that finished. That table will not only hold files that downloaded successfully to a 100%, but also downloads that were canceled or aborted. You could have a policy of deleting records from this table after some given number of days.

• The “live” downloads are in other table. Keep in mind that for the sake of simplicity, the migration of records from the “live” table to the “historic” one should be asynchronous (maybe a cron job or timer), meaning completed download will live there for a few seconds, or minutes after they finished. There’s nothing wrong with it. It’s even desirable so files don’t disappear from view the very millisecond they finish downloading.

• Add two timestamped columns to each table: TimeAdded and LastModified.

• Why do I say this is both ? As I see it, it’s a combination of the two approaches. Table one behaves as if you’d chosen the single table approach. Only an asynchronous process migrates completed or aborted files to a historic table after some time, it doesn’t have to be immediate.