Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have to create a table where each row needs to store 50 number values. Each row will always need to store 50 number values.

If this was a smaller number of values, I would just make fields for each of the values, but because there are 50, this approach seems a bit cumbersome (but since it will always be 50 values, maybe this is the correct approach?).

Is there a way to store an array of values in a field? This seems like a nice solution, but the concept is almost identical to creating a relational database.

share|improve this question
2  
Don't use an array ina field, that is the worst possible way to store the values. –  HLGEM Jun 23 '11 at 19:40
3  
How are you planning to use the data? Will you always need to see them in one row or will it matter if you have multiple rows for differnt values? Are they all the same data type? –  HLGEM Jun 23 '11 at 19:41
1  
You might be able to store the 50 numbers in an array, if you tell us what database you're using. Some, like Oracle and PostgreSQL support array-types in columns. Not sure if MySQL or MS-SQL or others support this functionality. But if you have to query these numbers, you might want 50 columns (and maybe even index the important ones). –  FrustratedWithFormsDesigner Jun 23 '11 at 20:00
7  
Sooprise, you need to clarify if you are talking about 50 different properties (height, weight, cost, etc), or if you are talking about an array of 50 identical items that differ only by index. –  GrandmasterB Jun 23 '11 at 20:07
    
The answer by GrandmasterB is better.You need make sure the 50 number values are fixed or not. if fixed use 50 columns maybe is ok. if the number values are not fixed, no choose save each number per row –  ericW Dec 9 '11 at 2:40
show 1 more comment

8 Answers

I suggest storing each number in a separate row, with the same key.

Then, to retrieve all 50 values, do SELECT * FROM table WHERE key=:row_key. That retrieves all the values, you can iterate through them and place them in an array or whatever.

You could also add a sequence number if you need to preserve order or have fully unique keys.

KEY  VALUE
---  -----
 1    5
 1    8
 2    6
 2    45

With sequence number:

KEY  SEQUENCE  VALUE
---  --------  -----
 1      1        5
 1      2        8
 2      1        6
 2      2        45
share|improve this answer
    
Why would this be good? Doesn't it cause the database to have to do a larger search? –  Crazy Eddie Jun 23 '11 at 19:50
    
I think this assumes that the sequences of 50 numbers can be normalized...? I just assumed that the 50 numbers per row could be completely random. Would this still work in that case. –  FrustratedWithFormsDesigner Jun 23 '11 at 20:03
    
@Frustrated: What I understand from the OP is that there are 50 numbers associated with one key. He doesn't say what exactly they signify. If they are properties, they are fixed and what Grandmaster suggests is exactly right. If they aren't, though, the number is likely to change and a more flexible design is called for. –  Michael K Jun 23 '11 at 20:18
    
@Crazy: With proper indexing, the search times should be neglible. Integer keys are among the fastest to search, and 50 records or so is a small dataset. –  Michael K Jun 23 '11 at 20:19
1  
@JeffO - do you really believe that it won't ever change from 50? I believe that for 1, maybe even 2, but once you've got as many as 50 it's pretty much gratuitous (unless it's something very specific like US states) and could easily change and I wouldn't believe any business that told me it wouldn't. –  Jon Hopkins Jun 24 '11 at 12:57
show 3 more comments

If we're talking, for example, tracking 50 different distinct properties of an item or object, then yes, make 50 different columns. Its not cumbersome at all.

If on the other hand you're talking 50 indexed values, from 1 to 50, what Michael suggests may be better as it will let you alter the number of items stored per row without altering the schema of the database.

share|improve this answer
    
Yes, if they are properties then they should be columns. Although I'd wonder if there wasn't another table hidden in there somewhere... –  Michael K Jun 23 '11 at 20:20
    
The issue here is that if you've got 50 similar properties, I'd personally have no faith that those properties won't either be added to or amended in the future. At that point you're altering the structure of the database (potentially significantly) to deal with what is basically a data change. For more than a small number of values I'd always denormalise it, regardless of what anyone told me about the data never changing. –  Jon Hopkins Jun 24 '11 at 12:49
add comment

If you will need to retrieve most or all of these values from the DB at once, I see no reason not to simply create a "wide" table containing each number as a column on each row. If you would like to project this to and from an array in your code, it is usually trivial to do so using an ORM.

If you usually only have to retrieve one, or a few, of these numbers at a time, or it is useful to get "columns" of numbers across several records, or you don't have advanced domain mapping tools at your disposal, you may consider a "key-question-answer" table structure. The "key" identifies a larger record made up of several rows in the table. The "question" is an identifier for an individual property belonging to one record (in your case the ordinal of each of the 50 values), and when combined with the "key" is a unique index. The "answer" is the value of the given property on the given record. In the absence of a flexible ORM to generate SQL and transform data to objects for you, this is likely the best way to flexibly pull a set of numbers as a "stream" which you can use to iteratively populate an array with very little code.

share|improve this answer
add comment

The question is if you believe the "each row will always have 50 values". Who is telling you that? what are going to store those values? is really the probability the requirement for those values change absolute zero?

Everytime i've been told that something will never change, it goes and change. So you need to exercise a bit of your own criteria about that.

If you still believe that its truly going to be eternally 50 values per row (like, the table is for penta-decahedral objects, and the 50 values are the vertices or whatever), then, by all means, go ahead and add 50 fields

share|improve this answer
add comment

I'm a fan of using the most natural structure. If it is 50 attributes (e.g. you are just logging data) then use a wide table. If you think it the requirement will change then go vertical.

The vertical approach will require more storage for the data and indexes plus more I/O to read/write. But it will also be better at row locking b/c you will only be locking one attribute to update instead of all 50. That is just the theory, it all depends on exactly what you are implementing and what you have to deliver. YMMV

Do not store an array in a column. It's not even First normal form and you cannot update/count/sum/min/max/etc a single attribute if you do that. Keep it simple and clear. You don't want your 'very excellent design' that 'solves' are very simple problem appearing on thedailywtf.com in a few years.

share|improve this answer
add comment

Ok, if noone else is gonna say it, I will: consider using a NoSQL data store. There's a number that spring to my mind:

In the end you must design your database (including the choice of the data store) to make the most important and frequent queries straightforward (and thereby also fast). There is no general answer to that.

Also, from your profile I inferred you're a C# programmer. There should be a number of C# persistence frameworks which make this decision for you, based on the knowledge their developers put in them.

In the end I'd advise you to encapsulate this detail, so that you can change this implementation detail with only one single place of the access code having to be changed.

As for SQL, I would go with the solution Michael proposed, because it is simply more flexible and the data can be retrieved with a simple join. If you later realize this is a performance bottleneck, you can still quite easily move the data right into the rows, provided that you've encapsulated this choice.

share|improve this answer
add comment

First, @GrandmasterB has it right when he says, "Sooprise, you need to clarify if you are talking about 50 different properties (height, weight, cost, etc), or if you are talking about an array of 50 identical items that differ only by index."

Secondly, if you have some kind of ORM, you can hide the implementation behind that. I have some cases where the original data was de-normalized, and I used the ORM to allow access by index, so it doesn't really matter if you store each value in a column (VALUE_00) or in a separate child table, the rest of your code won't care.

Third, as everyone else said, don't try to store more than one value in one column (an array in one field). The problem is that you rob the database engine from the ability to index and search efficiently on that column. (I have seen a case where someone just stored XML data in a field - and with SQL Server's newer versions, it can actually parse that, but I still don't consider it to be a good idea.)

share|improve this answer
    
Aside from the obvious problems with storing data in XML, using one method on top of another just lengthens your retrieval times. –  Michael K Jun 24 '11 at 19:43
    
+1 for excellent distinction between storing values for the same attribute vs. storing a value for 50 different attributes. Really good point. Normalization wins if the values are for the same attribute. –  Emmad Kareem Dec 9 '11 at 2:57
add comment

I would not use an array column. If the 50 numbers represent a single value, it could be stored as a string like a phone number, but you're limited in what you can do or forced to unpack this mess too often.

With 50 fields it's easy to query, "show all the rows where column5 = 12 and column27=36", but it's harder to find, "rows with any column value = 27"

How the data are entered into this system may drive the method you use. If they come in batches that do not get updated but mostly appended, you could store it both ways, but disk space is an issue.

Either way, I don't think you are painted in a corner. If you find that there is a prefered way to retrieve the data in all/most of your queries, change it. The further along you go, the harder this will get.

How about some specifics on what you plan on doing with these 50 numbers?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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