Re: stooping lower

@juster

I just really just leaving room for the idea that someday the data might consist of two digit numbers, or perhaps even more.

@klassa

The multi-row method is exactly what I need, just stored differently, and if I say archive that everyday, that will only leave me with 365,000 rows by the end of the year, which isn't bad at all.

Thanks to everyone again for the quick responses!

Comment on Re: stooping lower

Replies are listed 'Best First'.
Re^2: stooping lower by jhourcle (Prior) on Aug 31, 2008 at 15:40 UTC
I just really just leaving room for the idea that someday the data might consist of two digit numbers, or perhaps even more. If you think they're going to stay within 255, you can pack them into a string, and then just store it in a char field. (not varchar -- no need for the extra overhead, as you know how many values there are going to be -- if the number of values change, you can then always alter the table Even leaving yourself two bytes per stored value is only going to be one character more than a comma-separated string is going to be. (and if you're using a varchar, that extra character overhead is moot) ... But that being said, there's a few other considerations that probably won't come out without benchmarking trying the single-record-per-user vs. the record-per-value approach -- How often are the values being updated? Are the values needed singly, or a whole lot at a time? Are the values updated singly, or many at the same time? And as you mention archiving -- you might want to consider that your archiving might not need to match your storage for normal use. Even if you store the data for normal use as a record per value, you can store it in a more compact format for long term archiving.	[reply]

Replies are listed 'Best First'.

Re^2: stooping lower
by jhourcle (Prior) on Aug 31, 2008 at 15:40 UTC

I just really just leaving room for the idea that someday the data might consist of two digit numbers, or perhaps even more.

If you think they're going to stay within 255, you can pack them into a string, and then just store it in a char field. (not varchar -- no need for the extra overhead, as you know how many values there are going to be -- if the number of values change, you can then always alter the table

Even leaving yourself two bytes per stored value is only going to be one character more than a comma-separated string is going to be. (and if you're using a varchar, that extra character overhead is moot)

...

But that being said, there's a few other considerations that probably won't come out without benchmarking trying the single-record-per-user vs. the record-per-value approach --

How often are the values being updated?
Are the values needed singly, or a whole lot at a time?
Are the values updated singly, or many at the same time?

And as you mention archiving -- you might want to consider that your archiving might not need to match your storage for normal use. Even if you store the data for normal use as a record per value, you can store it in a more compact format for long term archiving.

[reply]