0

For the following table structure, for the metric_id column, what are the advantages of having a JOIN dictionary table versus an ENUM dictionary defined in the code? Assuming the metrics dictionary is very rarely modified, the total number of metrics is around 300.

CREATE TABLE `object_metrics` (
  `object_id` int(11) NOT NULL,
  `metric_id` smallint(6) NOT NULL,
  `value` float DEFAULT NULL,
  PRIMARY KEY (`object_id`,`metric_id`)

Assuming object metrics contains roughly 1M rows.

Improve this question
5
  • Are we talking an SQL defined Enum, or are we talking <favourite language> Enum and passing the ids into database?
    – Kain0_0
    Commented Jul 8, 2020 at 13:11
  • language defined enum (stored in some yaml file)
    – Dunams
    Commented Jul 8, 2020 at 13:17
  • 1
    ... Which isn't an Enum. Its a custom data structure defined in an hierarchical data format. I believe what you are asking is: Should I maintain this infrequently changing data within a database engine, or should I define it within the application. I've already answered this over here.
    – Kain0_0
    Commented Jul 8, 2020 at 13:55
  • 2
    Does this answer your question? Storing static data idea — is it best practice?
    – Kain0_0
    Commented Jul 8, 2020 at 13:55
  • @Kain0_0 Thanks, you raise fine points and I'm personally for storing it in a database dictionary. I'm debating this with colleagues that think otherwise. Also, let me clear-up on the enum, it will be imported from a structured file into a rails application and defined as an enum for the Active Record model.
    – Dunams
    Commented Jul 8, 2020 at 16:47

2 Answers 2

Reset to default
1

For small projects, having a JOIN table on identifiers has rarely any advantage over simply listing the identifiers in the programming language itself. This changes when you move to bigger systems and enterprise.

The primary data store of your enum values is suddenly not your database, but instead a system responsible for the list of values (small static tables), and each related system has the option to replicate a given static table to its own database. There are also multiple environments, clusters,...

Making a replication from one database to another (where the database engines are likely to even be the same, e.g. Oracle to Oracle) is a much easier task than making a replication in terms of database to programming language.

The latter most likely requires introducing logic to different programming languages (since different systems may use completely different approaches), ability to somehow commit to VCS the propagated changes, configuration of structural positions of such generated code,...

If your project is small, perhaps having the enums hard coded directly in the database may be feasible. But even then, it might be a better idea to store the enum names directly in the database, rather than storing arbitrary created and incremented identifiers decided only in your programming language of choice (e.g. storing NEW instead of 1).

Improve this answer
1

There is only one situation that I should store my enum values to a database: they are unimportant and tend to change frequently.

  1. If a value actually controlls your processing flow, e.g. appears in an "if" condition, it should be a strongly typed variable, otherwise you are using magic string/numbers.
  2. If a value actually controlls your processing flow, you have to modify your code anyway when it changes.
  3. Storing enums in a database adds extra, redundant layer to your program.
  4. With modern CI/CD infrastructure at hand, "changing the code" is not horrible as 20 yrs ago.
  5. If these varibles are non-trivial, you need a full regression no matter the change is a line of code or a row of a table, right?
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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