5

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!

Improve this question
3
  • 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.] Commented Oct 6, 2011 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. Commented Oct 6, 2011 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. Commented Oct 6, 2011 at 21:02

2 Answers 2

Reset to default
13

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

Don't.

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!

Improve this answer
3
  • 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.) Commented Oct 6, 2011 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? Commented Oct 6, 2011 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. Commented Oct 6, 2011 at 19:48
9

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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.