Design: Persist or Derive? Part II

Part I is here.

I realized near the end of that post that I had completely screwed it up. I think some of the intent was conveyed, but not really what I wanted. I'm going to try it again.

New sample tables: ONLINE_STORES and ONLINE_STORE_STATUS.



ONLINE_STORES has 4 columns:

• ONLINESTOREID - sequence generated surrogate key
• NAME - String, can be anything really
• START_DATE - When this store went online
• END_DATE - When this store went offline

ONLINE_STORE_STATUS - The purpose of this table is to capture the history of a store's status, either up (available) or down (unavailable). A record is created when the store goes down, it can be either a pre-determined amount of time where END_DATE is set to a time in the future (say 30 minutes) or the END_DATE can be NULL and must be manually re-activated. It has the following columns:

• ONLINESTORESTATUSID - sequence generated surrogate key
• ONLINESTOREID - FK referencing ONLINE_STORES PK
• REASON - Why was the store de-activated or brought down. Typically I would supply a list of known reasons, but it's unnecessary for my purposes.
• START_DATE - Time the online store was de-activated.
• END_DATE - Time the online store was re-activated.

What about Persist or Derive?

Similar to the example from Part I, you could do this (find an online store's status) another way be storing the status inline in ONLINE_STORES. Add a column (Persist) STATUS with a check constraint that limits the values to either UP or DOWN, along with a NOT NULL constraint of course.

In a pure OLTP environment that is probably the most efficient solution. Reporting on down times, or better, how long has an online store been UP, is sometimes an afterthought. This can be handled by a shadow/history/audit/logging table. Those have always felt clunky to me.

Many systems are a hybrid of OLTP and reporting (DW/DSS/etc.). My approach has been to tie the two tables together using a View (Derive) to get the answer to whether an online store is UP or DOWN. There might even be a name for that in the modeling books...I should read up.

Here are the scripts necessary for my demo:

CREATE TABLE online_stores
(
  onlinestoreid NUMBER(10)
    CONSTRAINT pk_onlinestoreid PRIMARY KEY,
  name VARCHAR2(100)
    CONSTRAINT nn_name_onlinestores NOT NULL,
  start_date DATE DEFAULT SYSDATE
    CONSTRAINT nn_startdate_onlinestores NOT NULL,
  end_date DATE
);

CREATE TABLE online_store_status
(
  onlinestorestatusid NUMBER(10)
    CONSTRAINT pk_onlinestorestatusid PRIMARY KEY,
  onlinestoreid
    CONSTRAINT fk_onlinestoreid_oss REFERENCES online_stores( onlinestoreid )
    CONSTRAINT nn_onlinestoreid_oss NOT NULL,
  reason VARCHAR2(100)
    CONSTRAINT nn_reason_oss NOT NULL,
  start_date DATE DEFAULT SYSDATE
    CONSTRAINT nn_startdate_oss NOT NULL,
  end_date DATE
);

INSERT INTO online_stores
  ( onlinestoreid,
    name )
VALUES
  ( 1,
    'online store #1' );

INSERT INTO online_stores
  ( onlinestoreid,
    name )
VALUES
  ( 2,
    'nerds r us' );

INSERT INTO online_stores
  ( onlinestoreid,
    name )
VALUES
  ( 3,
    'geeks rule' );

CJUSTICE@TESTING>SELECT * FROM online_stores;

ONLINESTOREID NAME       START_DAT END_DATE
------------- --------------- --------- ---------
     1 online store #1 30-JUL-09
     2 nerds r us      30-JUL-09
     3 geeks rule      30-JUL-09

3 rows selected.

Now I just create a simple View on top of these 2 tables:

CREATE OR REPLACE
VIEW vw_active_stores
AS
SELECT
  os.onlinestoreid,
  os.name,
  os.start_date,
  os.end_date
FROM online_stores os
WHERE NOT EXISTS ( SELECT NULL
                   FROM online_store_status
                   WHERE onlinestoreid = os.onlinestoreid
                     AND ( end_date IS NULL
                      OR SYSDATE BETWEEN start_date AND end_date ) );

Create a record in ONLINE_STORE_STATUS:

INSERT INTO online_store_status
  ( onlinestorestatusid,
    onlinestoreid,
    reason )
VALUES
  ( 1,
    1,
    'maintenance' );

Select from the View:

CJUSTICE@TESTING>SELECT * FROM vw_active_stores;

ONLINESTOREID NAME       START_DAT END_DATE
------------- --------------- --------- ---------
     3 geeks rule      30-JUL-09
     2 nerds r us      30-JUL-09

Voila! I Derived!

As a brief sanity check, I created a record that had a pre-determined re-activation date (1 hour forward).

INSERT INTO online_store_status
  ( onlinestorestatusid,
    onlinestoreid,
    reason,
    start_date,
    end_date )
VALUES
  ( 2,
    2,
    'the geek sliced us...',
    SYSDATE - ( 1 / 24 ),
    SYSDATE + ( 1 / 24 ) );

CJUSTICE@TESTING>SELECT * FROM vw_active_stores;

ONLINESTOREID NAME       START_DAT END_DATE
------------- --------------- --------- ---------
     3 geeks rule      30-JUL-09

1 row selected.

I'm really not sure which way is better/worse, as with anything I guess "It depends." The semaphore (flag) in ONLINE_STORES is a perfectly viable solution. It is the easiest solution, admittedly. Part of my thinking as well, and this relates back to the question I posed once before, UPDATEs in OLTP: A Design Flaw?. If I UPDATE the record in ONLINE_STORES, it has meaning. Typically it would either be to change the name or set the END_DATE. The UPDATE in ONLINE_STORE_STATUS means something else, it's just telling me the stop time of the DOWN time.

Or I am just overthinking this? Is this too much idealism? Is it idealism at all?I've talked about it so much lately I can't remember which way is up. What do you do?