Let's say I am storing start and stop points per user into a database table.

For example... let's say in a chat system, a user only needs to see lines 24-293, and 500-512. (Let's say he logged off for the night, and was not present between lines 294-499).

Saving each start and stop point as a discrete row would lead to a huge ballooning table, which isn't exactly ideal. I wouldn't be searching on these points, so indexing is not necessary.

How would I save this kind of information in the database?

I was thinking of saving it in JSON:

accountID   |   lines
        1   |   [24,293,500,512]

Where each odd value denotes a start, and each even value denotes an end. My only worry is that highly active users will be joining and leaving a chat, and would quickly fill up the row if lines was of type VARCHAR.

So perhaps TEXT, or even LONGTEXT.

Am I heading down a path of hurt, or is this a viable method?

Thanks in advance!

  • 1
    "lead to a huge ballooning table"? How huge? How quickly does it grow? What constraints on storage do you have? [I'm trying not to be dismissive of your question, but this may be a non-issue.] – S.Lott Oct 6 '11 at 19:07
  • No worries, S.Lott. My initial worry was that having one record for each start/stop event seemed wasteful, in that dedicating "an entire row" to just that little tidbit of information, over and over, and over again. Looks as though I am quite misinformed. – Julian H. Lam Oct 6 '11 at 19:30
  • I see that you decline to provide information on size or growth or constraints. That makes it difficult to comment on your concerns. Moderators have told me that I am not allowed to clarify questions. I'm forced (by the moderators) to point out that I am unable to respond to the question as written. Thanks for repeating the essence of the question, however. – S.Lott Oct 6 '11 at 21:02

You're trying to denormalise separate facts into a single record, rather than storing them as individual records.


You appear to believe that it will be easier and more efficient to do parsing of pseudo-sets yourself than to store them in the normal way. That is possible but unlikely unless you actually tried it out and determined that your database has problems with the amount of records you would create. But as long as the facts are created by manual clicking activity of users, it is very very unlikely that your database will run out of rows before you run out of processor cycles manipulating the ever-growing strings you would need. Play to the strengths of the components you use. Databases are for storing millions upon millions of rows. Try to outperform them at your peril!

  • Was writing something similar. Properly indexed, storing rows of start/stop lines and joining them with rows in the table that holds the lines themselves will be very efficient and perform very well. (Personally, I'd keep track of everything with timestamps instead of trying to associate line numbers. The latter will cause all sorts of very obnoxious problems.) – Blrfl Oct 6 '11 at 18:08
  • Thanks for alleviating my fears, Kilian. Lesson learned (hopefully). @Blrfl, it's possible to store timestamps, but wouldn't I then have an extra translation layer to go through in rendering the data? Chat -> Timestamps -> Lines/Messages , instead of Chat -> Lines/Messages? – Julian H. Lam Oct 6 '11 at 19:36
  • 1
    Not if you put timestamps on the rows and don't try to use row IDs to express sequence (which isn't a good idea anyway). If you do everything by time, you can join the session rows with the chat rows with something like WHERE chatlines.timestamp BETWEEN sessions.login_time AND sessions.logout_time and you'll get just what you want. – Blrfl Oct 6 '11 at 19:48

First off, database are good and storing lots of rows. That is something that is ideal for a database.

If you know you will never need to do more with the data, and you can guarantee that the string size has a maximum, then it won't hurt, just cause some extra parsing cpu cycles. (But don't be surprised at the fact that things aren't always predictable.)

If you find you need the following, you've just created yourself some pain:

  • overloading the json to include more information because it's easier than maintaining a database schema
  • finding that you do want to search or sort numbers, or join to other tables
  • good debugging on the process that inserts, updates and deletes the data
  • some users hit the edge case of needing so much string data that the field is truncated

At the point that you do decide to switch to table data, you'll need to create a process to parse the string and insert new rows into the new table. For your current json spec, that shouldn't be too bad to write. (As long as you didn't overload the json.)

Storing numbers in a database as a string adds an extra level of processing that isn't necessary. You now have to parse the string. You'll still need to store the numbers if they are table rows or a string, so size shouldn't really matter. I wouldn't worry about the number of rows in the database, indexes will do just fine.

Sticking with one format (database) instead of two (database + json) is never a bad idea.

I would create a secondary table for just the start and stop points with an index on account id. Then you can rely on database features to provide searching, sorting and caching.

accountID | start | stop
        1 |    24 |  293
        1 |   500 |  512

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.