I'm new to databases, please help me to figure out the best design to store 8-digit numbers that are going to be used for two purposes:
- Statistical analyses of separate digits. For example all 2nd digits across all entries or frequency of appearance of a particular digit etc.
- Matching whole number to find if the number is in the database in order to get data from relevant columns.
The usage ratio between these two purposes is expected to be close to 50/50. The initial amount of entries is around 6000, with yearly growing close to 2000 entries. Chosen DMS is MariaDB (MySQL).
I can see the 3 possible ways of implementing this:
- Store whole numbers and split them each time via programming languages later (for usage #1).
- Make 8 columns and store numbers as separate digits. Concatenate them each time via programming language later (for usage #2).
- Store both, the whole numbers and separate digits in 8 columns.
But personally I'm not excited about any of these ideas of mine, so I'm seeking an explanatory advice from you.