0

I have a website that allows users to be "tracked" and track their time/work. This involves the users being able to update their account with my site at most every minute, but typically users do it only a few times per day. However, there is nothing stopping other people from updating your account.

Each update creates a datapoint, a snapshot if you will, of their account status and the amount of work they have done.

Users can view the amount of work they have done in the past day or past week. This is done by changing the range of the datapoints you want to see.

My question now, is: There is the very likely chance of having 300k+ users. There are also some user accounts which people follow more closely than others, say, a ceo or a manager, and so those accounts are going to be updated more often. This leads to there being a possible 100k+ data points for a single user, within the timespan of only a year or so.

Currently I was thinking of just storing these by the following mapping:

unixtime -> account snapshot

This seems like it would be easiest to store as a large json array for a user, for example (with other details I would store):

jsonArray = {
    "alice": {
        "totalHours": 31.6,
        "updates": {
            1515653260 : { work: 95%, hours: 8 },
            1515691399 : { work: 93%, hours: 10 },
            1515695125 : { work: 91%, hours: 7.6 },
            1515698694 : { work: 56%, hours: 6 },
         }
    }
     "bob": {
        "totalHours": 7.32,
        "updates": {
            1515654356 : { work: 95%, hours: 1 },
            1515690342 : { work: 93%, hours: 6.32 },
         }
    }
}

Is this an effective solution? I can't imagine a json string taking up so much space that MySQL wouldn't be able to hold it but I've never dealt with things that are likely to grow this large.

Are there other data structures I could use, or that would be more efficient in grabbing/storing data?

2

Your scoping requirements (300K+ users, 100K+ bytes per user) suggest the use of a relational database such as MySQL, or a document data store like MongoDB, not a JSON array. JSON is primarily a data transfer mechanism, not a storage mechanism, and it lacks the necessary tools (indexes, querying) that you'll need to effectively work with that much data.

  • Sorry for my limited SQL use, is there a good way to store some type of one-to-many mapping in MySQL? Thanks for the input. – sej Jan 11 '18 at 21:27
  • Of course. All SQL databases support one-to-many relationships. – Robert Harvey Jan 11 '18 at 21:38
  • 1
    JSON is just a text representation of data, it is no more useful as a database than a .csv file is. – user7519 Jan 12 '18 at 0:25
1

From my perspective, there might be a number of things here I would do differently. Firstly, when you have a system with a potentially large number of concurrent users, it's pretty important to make sure you aren't updated shared resources, because this makes the users' operations all convoy on a single lock.

Secondly, you lose most of the benefits of having a database at all if you just store all your data as a JSON blob. It isn't indexed, it can't be queried without loading into memory etc... You should think about how to model your data to achieve your functional and non-functional requirements, rather than just dumping it all into one object.

Lastly, if you're planning on using JSON as an interchange format between your code and database, you might consider a document database solution of some sort, which would speak native JSON or BSON.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.