Question: In my application, as user can have up to 64 medals on their account.

Each medal has a value from 0 to 3 (takes 2 bits).

Question: How should I store the medal values in the database?

Option 1:

```
 USER_ID   MEDAL0   MEDAL1   MEDAL2  ... MEDAL64
    1        0        1         2           0

```

Option 2:

```
 USER_ID   MEDALS
    1       blob

```

Additionally, the medals are broken into 8 sections that typically go together, so they could in be combined in the database like so, but concatenating the bits into a single integer:

```
 USER_ID   MEDALS1   MEDALS2   MEDALS3  ...  MEDALS8
    1      200260      2332     69188           0

```

So my question is, how do these options differ in terms of SQLite performance (speed and to a lesser extent memory)? Is it ok to have 64 columns which contain 4 bit INTEGERs? Better to have 8 columns with large INTEGERs?

Answer: 
As for the speed. There are 3 kinds of speed. Write speed (**W**) including initial write and update, read speed (**R**) and program writing speed (**P**).

I would prefer **Option 1**.

With option 1, you get the fastest **R** and **P** because when you need to read and write a medal, you just SELECT and UPDATE the corresponding field. With option 2,3 you must read additional bits. And when you need to write, you **have to read the old bits**, update it and save the new composed value.

The **initial W** may seem slow at first glance but actually it's not. Sqlite will detect how big the number is and store it in the most compact way.
On the [SQLite datatypes page, section 1.0](http://www.sqlite.org/datatype3.html) it goes into detail how the INTEGER values are stored and retrieved. The upshot of that paragraph is that integers may be stored at a specific length but when they are used in memory they are 64-bit (signed) integers.

Option 2 is probably the most flexible. You can add more classes of medals (3+ bits), and more medals. It also might be more inconvenient than dealing with some kind of integer.

Option 1

> 
> Is it ok to have 64 columns which contain 4 bit INTEGERs?
> 
> 
> 

The smallest storage class for integers is 1 byte on disk, so half of that space will be wasted, in addition to the unused space at runtime. But it might be easiest to deal with from a programming perspective.

For Option 3, I'd at least consider going to a larger integer size (`UNSIGNED BIGINT`, 64 bit) to reduce pasting bits.

I'd at least try Option 1. SQLite is reasonably fast, and it would at least get you going.