Database Design – One Table with a Flag or Two Tables

database-designsqlite

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.

Best Answer

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.

Related Question