I have noticed in my SQLite database that some tables contain columns where many rows usually repeat the same data.

**Transaction**

| key | from\_account\_key | to\_account\_key | timestamp | amount | data\_type | data |
| --- | --- | --- | --- | --- | --- | --- |
| 200 | 20 | 40 | 1608320428 | 8 | "BUY" | {blob 1} |
| 201 | 20 | 40 | 1608320429 | 20 | "BUY" | {blob 1} |
| 202 | 20 | 40 | 1608320429 | 20 | "BUY" | {blob 1} |
| 203 | 20 | 40 | 1608320430 | 10 | "BUY" | {blob 1} |
| 204 | 20 | 3 | 1608320434 | 10 | "TRANSFER" | {blob 2} |
| 205 | 20 | 3 | 1608320440 | 20 | "TRANSFER" | {blob 2} |
| 206 | 20 | 3 | 1608320480 | 10 | "TRANSFER" | {blob 2} |
| 207 | 26 | 40 | 1608320510 | 20 | "SELL" | {blob 1} |
| 208 | 40 | 26 | 1608320530 | 20 | "REFUND" | {blob 3} |
| 209 | 20 | 40 | 1608320533 | 26 | "BUY" | {blob 4} |

For example here in this table of transactions you can see that the sender account often sends to the same recipient account, thus there are many repeating cases of `( from_account_key, to_account_key )`. You can also see that `( data_type )` and `( data )` have identical values in many rows as well.

My idea is to factorise these repeating non-single-integer columns into their own tables and then reference them by an integer key, so the repeating values can be reused. (Doing this to this table would result in the tables `Transaction ( key, transaction_accounts_key, timestamp, amount, data_type_key, data_key )` , `TransactionAccounts`, `DataType` and `Data`).

Upsides of this is less repeating data, leading to a smaller database. Downsides are that upserts are more difficult since there are more tables and more logic is required, queries become more complex, and most of all that more joins are required to retrieve the original columns.

So my question is, how far should I go with this? Is there tradeoff between performance and space here?

What I know is that the `( data )` blobs may be relatively big, but repeat many times, so I would definitely put them in their own table and refer to them by an integer key. I would also do this with `( data_type )` because strings may take up relatively much space. But to me this seems a bit overkill for 2 integers `( from_account_key, to_account_key )`, so where do you draw the line?