sqljsondatabaserdbmsobjectdb

Which is better SQL database or json file?

I am creating an application where large number of data will be stored in the server. For example a to-do list.

A user comes, writes down his to-do list like a list of 100 tasks for each day. So, all the task will be shown to him like a list (of 100 strings).

Which will be better to store the data:

  1. A SQL table

    todo_table(datetime, user_id, todo_string)

  2. A JSON file

    For each user, there will be a folder, and a day folder within them like -

    abc_user(folder) 

     --> 12/01/16(folder) --> json file
 --> 13/01/16(folder) --> json file
 --> 14/01/16(folder) --> json file

    .. and similarly for other users.

    Each json file will have an array of objects like

    [
    {
        "time":"12:05",
        "task":"Wake up"
    },
    {
        "time":"01:10",
        "task":"Read"
    },
    {
        "time":"03:15",
        "task":"Dance"
    }
    .
    .
    .
]

Please tell me which would be the better approach with respect to latency, efficiency, security.

Can SQL handle such large number of data in a single table? Remember, that each user will make up to 100 entries each day and if 1000 users do the same, the total entry will be 100,000 per day.

Solution

  • A sql database is the only way to go. Any distribution of SQL will be able to handle this with ease. What you do on the server shouldn't affect latency. You have many options, including encrypting your data, depending on the SQL distribution to make your database secure. Assuming your indexing is solid SQL will be more efficient than looping through a file system and then parsing JSON files.