1

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:

  1. Statistical analyses of separate digits. For example all 2nd digits across all entries or frequency of appearance of a particular digit etc.
  2. 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:

  1. Store whole numbers and split them each time via programming languages later (for usage #1).
  2. Make 8 columns and store numbers as separate digits. Concatenate them each time via programming language later (for usage #2).
  3. 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.

  • What about storing the 8 digit code/number as a string and then using a functional index on the different characters? Or VIRTUAL fields derived from your 8 digit code. If these "numbers" are never added, divided or multiplied, then they should be stored as codes and not numbers - a bit like phones! BTW, I would recommend PostgreSQL over any MySQL deriveative - vastly superiour in virtually every way! – Vérace Apr 29 at 23:43
  • Thank you for your comment. Yes these numbers are never added, multiplied etc. they can be called codes. "stored as codes" you mean string data type, right? Is it rational to use VIRTUAL fields if I need the data on the "constant basis". I mean would it be rational to make SQL split the code to digits every single time I select thousands of codes versus storing them as digits in separate columns? Sorry, quite new to this. – Mike Apr 30 at 0:51
  • 2
    Set them to CALCULATED - the calculation will be done on every query for any character [0 - 7] you choose. You have a really small amount of records so that won't be a problem. I have to go to bed now (it's 0200 in the morning), but I can write this up as an answer with a worked example or two tomorrow if you think that I've pretty much answered your question? – Vérace Apr 30 at 0:54
  • What is the preference? increase performance? decrease storage space? something else? – Akina Apr 30 at 5:25
  • Just do option 1. 6000 rows + 2000 a year is nothing, you can do it all in memory in no time at all. – Philᵀᴹ Apr 30 at 7:31
2

Your best solution for this is undoubtedly the use of GENERATED columns (also known as VIRTUAL or COMPUTED columns).

You mention MariaDB - the documentation is available here. The core bit (the syntax) is here:

<type>  [GENERATED ALWAYS]  AS   ( <expression> )
[VIRTUAL | PERSISTENT | STORED]  [UNIQUE] [UNIQUE KEY] [COMMENT <text>]

So, for MariaDB, I constructed the following table here (fiddle):

CREATE TABLE the_code_1
(
  code CHAR(8),
  the_code_location CHAR(1) GENERATED ALWAYS AS (SUBSTR(code, 3, 1)) 
  VIRTUAL COMMENT '3rd character'
);

Then I INSERTED some data:

INSERT INTO the_code_1 (code) VALUES ('12345678');
INSERT INTO the_code_1 (code) VALUES ('87654321');

Note that only 1 field is inserted! Then, I SELECT from that table:

SELECT * FROM the_code_1;

Result:

code    the_code_location
_________________________
12345678                3
87654321                6

Which is the 3rd character in both of those fields!

The table definition above has VIRTUAL, meaning that it's calculated on the fly. The fiddle also shows the use of the PERSISTENT keyword (in place of VIRTUAL), meaning that the data is physically stored on disk.

This is a fiddle using MySQL 5.7 - the only difference is that in place of the the PERSISTENT keyword, STORED is required. The MySQL syntax is here. This is also interesting:

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]  -- Note! no PERSISTENT keyword!
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

All in all, depending on your disk versus performance requirements, one of these two options is the way to go - you can do a few tests. Your requirements are minimal so either of the two options should work satisfactorily.

  • 1
    This gives you Choice 3 with minimal effort. – Rick James May 2 at 3:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.