Storing a large array in a database

Question

I have a really long list of itinerary items with integer values. I was thinking about creating a database with each of these items with like fifty columns but that seemed a bit long-winded, and the itinerary may change. Is there a better way to store these name => value pairs in a database? serializing seems a bit crude, I was thinking about just creating an itinerary_items db with name, value columns and sticking the data in there. What is the best solution?

Answer 1

You'd pretty much have a column for the key, and a column for the value, and insert the records, as you mentioned.

Answer 2

If the data you are storing isn't used for searching the database, and is only updated in application code, then I would lean towards just storing it as a blob. If you are likely to update portions of it and don't need to read the full set of data to make meaningful updates, then a separate table with columns like id, user_id, key, value would be the way to go.

Answer 3

Write out a for-loop to create you INSERT statement, then just execute the query to save the array into a table with key and value columns. To recreate the array, retrieve it from the table and use another for loop to reinsert the values into an array.

Only problem I can see in this kind of setup is that if you plan to have multiple instances of the array, and all these instances have to be saved, as the database table can only represent one instance of the array.

If planning to have multiple instances, best way would be to serialize and store in a blob or TEXT column.

Answer 4

Why not save them like like properties in a table. that is

| id| name       | value        |
+---+------------+--------------+
| 1 | array_key  | array_value  |
| 2 | array_key2 | array_value2 |

If one wants multiple instances of the array, one could always add an extra key for keeping track of the instances:

| id| name       | value        | instance_key |
+---+------------+--------------+--------------+
| 1 | array_key  | array_value  | 1            |
| 2 | array_key2 | array_value2 | 1            |
| 3 | array_key  | array_value  | 2            |
| 4 | array_key2 | array_value2 | 2            |

Problem with the approach of using this kinds of solutions is that the data tend to be more less human-readable and there be alot of extra joins if you have several properties on an object.

Answer 5

How many records do you have? Hundreds? Thousands?

If your record count is below 500 then I don't think you should pick up a database at all. You could store that in a XML file. These are the times of NoSQL :).

Not that I support the movement, but a database seems like overambition for your requirement.

Answer 6

Your best and simplest way is to use serialize.