Is this an acceptable situation to store an array in a database?

Question

I am developing an application and I came across a situation where I would like to take a snapshot of current data.

For example, in this application, users will have different statistics and will be able to enter matches. How they are placed in matches depends on their statistics. When matches are determined, the application will pull out all the current statistics of the user and determine their points to find out who wins.

Now, after the end of the match, I want users to be able to view past matches, and the problem occurs when I want to show what the participants indicate during the match. I would think it would be acceptable to store an array structured like this:

array( array(username, points), array(username, points), etc. ) 

Data normalization may now be best practice, but in this situation:

• There can be a place in a match between 2 and 25 participants.
• Data will never be updated, only read.
• I would have thought that having it in an array structure in a database would save me from having to create an array in my internal code.
• EDIT: Data is not persistent. Match records will be deleted 7 days after the end of the match.

Can someone tell me if this solution will give any problems?

---

EDIT

I would save the data after serializing the array, so in my database I would have a table called "matches" and it would have a column called "results".

Rows for this column will contain serialized arrays. So, if the array looked like that:

 $array["a"] = "Foo"; $array["b"] = "Bar"; $array["c"] = "Baz"; $array["d"] = "Wom"; 

Then the row in the database will look like this:

 a:4:{s:1:"a";s:3:"Foo";s:1:"b";s:3:"Bar";s:1:"c";s:3:"Baz";s:1:"d";s:3:"Wom";} 

Answer 1

If you are truly committed to the use cases that you indicated in the question, as well as to the qualifications in your commentary to Sean Johnson, I see no problem with your approach.

I can still qualify this by suggesting you normalize the data if you think you have a chance that you want to know historical information, but dropping the array into the database as a durable (relatively speaking) kind of cache may make sense. In other words, save it in both formats, but the main line of the use case you just use will hit the array, but you will still have the data in the requested form if you want.

Answer 2

This solution will not create any problems in the short term, but, say, you wanted to add functionality in the end to show all the games that the user played, or their highest winning games ... with this data in an inaccessible-out-sql -array will not allow you to have these functions.

I think a table like this would be ideal:

 CREATE TABLE game_scores( id int AUTO_INCREMENT NOT NULL PRIMARY KEY, game_id int, user_id int, final_score int, KEY(game_id),KEY(user_id) ) 

At the end of each game, you simply insert a line for each user who played this round with their respective score and game identifier. Later you can select all ratings for a specific game:

 SELECT * FROM game_scores WHERE game_id=? 

... or show all ratings by a specific user:

 SELECT * FROM game_scores WHERE user_id=? 

etc .. Enjoy it!