80 3 column rows, or 1 81 column rows

Question

I Have some data to store,

I must store this data for every user,

The data is more or less

key: 0  
value: 107,

key: 1  
value 213

There are about 80 key/value sets per user

So my question is,

Do I have one row that is basically

user_id key1, key2... key80

or do I have 80 rows of

user_id, key, value

---

Data I must store:
You can see the actual data i must store here: http://219.89.41.169/ReachApi/?gt=The%20hailwood

I Cant just copy/paste as it is approx 8mb of data, so the page may take a while to load.

But as you can see I am given the data in that format, Hence why I must store it this way.

Answer 1

I'd normally cringe at the thought of 81 columns in a table. However, IF:

• The number of keys/fields is unlikely to change, AND
• All of the fields relate directly to the user, and apply to every user, and/or
• You're likely to need to query for more than one key at a time for a given user,

then it makes more sense to have that many columns than to store each user/key combo as its own row. You get type safety, the ability to constrain and index your data better, and you get to query a user's stats without needing a dozen joins (or getting back a bunch of rows that you have to then assemble into a data structure).

If the number of fields is constantly in flux, or the fields don't apply to everyone (ie: some users would have different numbers of fields), or you never want to know more than user 3225's value for field 53, then a user/key/value table makes more sense. But it'll be a pain in the ass to keep everything correct, updates would take forever (because indexes would need to be redone, and you will need indexes), and queries will get hideous if you ever need more than one or maybe two fields at a time.