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.

  • Are we talking an SQL defined Enum, or are we talking <favourite language> Enum and passing the ids into database? – Kain0_0 Jul 8 at 13:11
  • language defined enum (stored in some yaml file) – Dunams Jul 8 at 13:17
  • ... 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 Jul 8 at 13:55
  • 2
    Does this answer your question? Storing static data idea — is it best practice? – Kain0_0 Jul 8 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 Jul 8 at 16:47
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 | |

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.