There are several tables in Mysql, for example, payments (payments), visits (visits), logs (logs) each of these tables has its own set of types (for example, payments include enrollment, payment for goods, payment for services, etc.). How best to store these types?

I consider several options:

  1. Use ENUM type
  2. For each of the tables, create a table reference book, where all possible types will be stored, and link them by key
  3. Create a universal table directory where all types and the corresponding table names will be stored (for example, 1 - payment - transfer, 2 - visit, input)

Which of them is most beneficial in terms of performance and the ability to expand the list of types?

    2 answers 2

    I do not undertake to assert that something is better. Hardly there is the only right decision.

    Enum is good because it gives a visual human-readable result in a sample without joins with reference books. That is, combines performance and visibility.

    On the other hand, reference tables may contain additional attributes, in addition to the identifier itself and decryption. Directories can be used as a source of values ​​for selection fields in an application. That is, in simple cases, you can extend the functionality without code changes.

    Versatility always argues with performance, and often with convenience. We need to know the measure :) P.М.SM. universal reference is already inconvenient.

    So I would advise you to choose from (1) and (2) and, possibly, to combine them. If you use any "migrations" together with the version control system, then there will always be order in the household, enums with reference books will not diverge.

      I think it is better to refuse the first option. Readability is its only plus, not the most critical one. Cons more. For example: this is a string type - it is still slower than a numeric one; storing numbers there, one must be careful not to get their indices instead of numbers - for example, counting the sum or there is an average; Any change to the list is ALTER TABLE.

      The second and third options are almost equivalent, but the minus of the third is that you have to store all values ​​in a compatible type (string or even binary), which again will negatively affect performance.

      I would recommend to dwell on the variant “one reference book - one table”.

      • The internal representation of ENUM is an integer. stackoverflow.com/q/259547/272885 - artoodetoo
      • This is yes. But how often is the work done with him? Not to mention the fact that the sorting by ENUM field is performed exactly by the index, and not by value - it freezes to death! - Akina