Store array of numbers in database field

Question

Context: SQL Server 2008, C#

I have an array of integers (0-10 elements). Data doesn't change often, but is retrieved often.

I could create a separate table to store the numbers, but for some reason it feels like that wouldn't be optimal.

Question #1: Should I store my array in a separate table? Please give reasons for one way or the other.

Question #2: (regardless of what the answer to Q#1 is), what's the "best" way to store int[] in database field? XML? JSON? CSV?

EDIT: Some background: numbers being stored are just some coefficients that don't participate in any relationship, and are always used as an array (i.e. never a value is being retrieved or used in isolation).

Bloodworth

Answer 1

The "best" way to store data in a database is the way that is most conducive to the operations that will be performed on it and the one which makes maintenance easiest. It is this later requirement which should lead you to a normalized solution which means storing the integers in a table with a relationship. Beyond being easier to update, it is easier for the next developer that comes after you to understand what and how the information is stored.

Dockage

Answer 2

Separate table, normalized

Not as XML or json , but separate numbers in separate rows

No matter what you think, it's the best way. You can thank me later

Kimsey

Hello, I come from the far future (2014). I'm here to thank you. – Clinic

@gbn, I am curious as why you think this is the best option instead of other alternatives. Could you please expand your answer? Thanks! – Bearce

this is very bad solution for a lot of applications. – Groos

Do you think this solution scales? I have an ordered array of 400K integers, I have a hard time thinking this is effective. As @jorge asks, could you expand? – Ungovernable

@MartinHansen This answer is from before the edit where we found out the array is treated as a block. If the array requires no DB parsing or work, then it is not a "series of numbers" from a DB perspective, but a simple object and can be stored as test or CSV or JSON or whatever. In many cases, this comes back to bite you when someone asks to parse that array for searching or export etc. There are many questions here on that subject. This is why DB types like me will recommend the separated approach.. and why this has x3 upvotes as the accepted answer... – Kimsey

Answer 3

Store it as a JSON array but know that all accesses will now be for the entire array - no individual read/writes to specific coefficients.

In our case, we're storing them as a json array. Like your case, there is no relationship between individual array numbers - the array only make sense as a unit and as a unit it DOES has a relationship with other columns in the table. By the way, everything else IS normalized. I liken it to this: If you were going to store a 10 byte chunk, you'd save it packed in a single column of VARBINARY(10). You wouldn't shard it into 10 bytes, store each in a column of VARBINARY(1) and then stitch them together with a foreign key. I mean you could - but it wouldn't make any sense.

YOU as the developer will need to understand how 'monolithic' that array of int's really is.

Bootery

Same for me here. It really depends on the relationship of data and if only single entries are needed to be updated or the whole array. I was saving audio peak arrays to a table containing mp3 data. those will never change, and if i would also have to also recalculate the audio peaks. => single json column was enough here – Chestnut

Answer 4

A separate table would be the most "normalized" way to do this. And it is better in the long run, probably, since you won't have to parse the value of the column to extract each integer.

If you want you could use an XML column to store the data, too.

Sparse columns may be another option for you, too.

If you want to keep it really simple you could just delimit the values: 10;2;44;1

Godinez

XML columns have some annoying restrictions, like not working from a linked server. – Freemasonry

Good to know, thanks. And in case I wasn't clear in my answer I am recommending a separate table as the appropriate solution for most cases. – Godinez

Answer 5

I think since you are talking about sql server that indicates that your app may be a data driven application. If that is the case I would keep definately keep the array in the database as a seperate table with a record for each value. It will be normalized and optimized for retreival. Even if you only have a few values in the array you may need to combine that data with other retreived data that may need to be "joined" with your array values. In which case sql is optimized for by using indexes, foreign keys, etc. (normalized).

That being said, you can always hard code the 10 values in your code and save the round trip to the DB if you don't need to change the values. It depends on how your application works and what this array is going to be used for.

Mercator

Answer 6

I agree with all the others about the best being a separate normalized table. But if you insist in having it all in the same table don't place the array in one only column. In instead create the 10 columns and store each array value in a different column. It will save you the parsing and update problems.

Hull

I don't really agree with that - sometimes you may need 2 columns, other times maybe you need 40. And if you have a bunch of optional columns in your table then they will just be taking up space. – Godinez

@Josh As I understand from the OP it will never be more than 10. – Hull