0

TLDR: How do I structure SQL for saving/retrieval of entity configuration data with ability to know which configuration is current (active) and with ability to keep and retrieve historical configurations that may no longer be active.

Details

A sales tool is used to quote products and pair them with a motor. There are M motors (motor frames) and each motor has P power configurations. Each configuration can change over time, so a historical record must be kept of each configuration. I need to know which configuration is active (only one can be active at one time) for a given (frame, power) key tuple. And if it is updated (a new configuration record is created for that key tuple), any of the existing quotes must still be tied to the original no-longer-active (frame, power) configuration set.

Sample Order of Queries

  1. A lookup request for current active configuration for frame.id = 1, power.id = 1. Version 1 comes back as active
  2. I write this configuration into Quote #1 using (frame, power, version) n-tuple
  3. Company updates frame.id = 1 and power.id = 1 to have a different set of attributes, which becomes version 2 and active. Old record becomes inactive.
  4. I create Quote #2 with this new data. Quote #1 must retain original configurattion.

How do I best structure/model SQL tables for this to happen?

Here is my preliminary design, where

  • frame table represents a motor frame (i.e. 215JM)
  • power table represents motor power requirements (380/60)
  • configuration is both frame and power as keys, along with a version (1, 2, 3 ..), and whether the configuration is active or not (boolean)
  • the payload is the remaining data in configuration table, i.e. horsepower (hp), efficiency

enter image description here

My question is:

  • does the configuration table need a unique identifier or is composite key ok?
  • does configuration table work as-is for my needs? i.e. do I need a 4th table specifically for historical records or is what I have now sufficient?

Here is the SQL if required:

CREATE TABLE frame (
  id INT NOT NULL,
  size VARCHAR(45) NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB;

CREATE TABLE power (
  id INT NOT NULL,
  power VARCHAR(45) NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB;

CREATE TABLE configuration (
  frame_id INT NOT NULL,
  power_id INT NOT NULL,
  version INT NOT NULL,
  active TINYINT NOT NULL,
  hp FLOAT NULL,
  efficiency VARCHAR(45) NULL,
  PRIMARY KEY (frame_id, version, active, power_id),
  INDEX fk_configuration_power1_idx (power_id ASC),
  INDEX fk_configuration_frame_idx (frame_id ASC),
ENGINE = InnoDB;
Dennis
  • 7,907
  • 11
  • 65
  • 115

1 Answers1

0

It depends on the how you intend to use the data. If you intend to use analytical queries (with heavy grouping / filtering by related tables or dimensions) maybe structuring your database like a Kimball-Style Star Schema is the way to go.

This will make your Configurations table a Slowly Changing Dimension Type-2

You can intercept the insertion, updates and deletes with triggers to implement the necessary logic to expire old records.

This will also mean that your table will have a new primary key (the Surrogate key) which would uniquely identify the records. Your existing Primary key - the version (frame_id, active, power_id) would become what is known as the business or natural key:

CREATE TABLE configuration (
  surrogate_key INT NOT NULL,
  frame_id INT NOT NULL,
  power_id INT NOT NULL,
  active TINYINT NOT NULL,
  hp FLOAT NULL,
  efficiency VARCHAR(45) NULL,
  effective_from DATE NOT NULL,
  effective_to DATE NULL,
  current_flag BOOL,
  PRIMARY KEY (surrogate_key),
  ...;

Then you'd join to the other tables using the Surrogate Key (which means that you're joining to specific versions of the natural key).

PS: Also is worth considering storing the effective_from and effective_to dates as integers (either EPOX or YYYYMMDD format) for easier filtering and joining.

Hope this helps.

Cristian Varela
  • 3
  • 1
  • 3