Database

Mycroft Holmes wrote:

that lives in a stored proc that does what it is told when it is told and everyone knows where to find it.

Richard Deeming wrote:

inserted

Richard Deeming wrote:

deleted

• sql trigger magic tables[^]: 413,000 results;
• sql trigger virtual tables[^]: 1,680,000 results;

SELECT
   A.ID As OriginalID,
   B.ID As DuplicateID
FROM
   -- Every pair of master records:
   MasterTable As A
   CROSS JOIN
   MasterTable As B
WHERE
   -- Only those pairs where A is earlier than B:
   A.ID < B.ID
And
   -- All detail lines for A exist for B:
   Not Exists
   (
      SELECT
         (relevant columns)
      FROM
         DetailTable
      WHERE
         MasterID = A.ID
      
      EXCEPT
      
      SELECT
         (relevant columns)
      FROM
         DetailTable
      WHERE
         MasterID = B.ID
   )
And
   -- All detail lines for B exist for A:
   Not Exists
   (
      SELECT
         (relevant columns)
      FROM
         DetailTable
      WHERE
         MasterID = B.ID
      
      EXCEPT
      
      SELECT
         (relevant columns)
      FROM
         DetailTable
      WHERE
         MasterID = A.ID
   )

DeerBear wrote:

I need tips on structuring a product database.

DeerBear wrote:

Free, Lite, Standard and Enterprise.
I need to model all four kinds possibly in one table and not just that, I also need to make sure that changes are trackable

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

CREATE TABLE PRODUCTS (
    ID                    INTEGER NOT NULL,
    PRODUCT_NAME          VARCHAR(255) NOT NULL,
    PRODUCT_DESCRIPTION   BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL,
    DOWNLOADABLE          INTEGER NOT NULL,
    PRODUCT_DISCONTINUED  INTEGER NOT NULL
);

CREATE TABLE PRODUCT_DETAILS (
    ID            INTEGER NOT NULL,
    PRODUCT_ID    INTEGER NOT NULL,
    HIGH_VERSION  INTEGER,
    LOW_VERSION   INTEGER,
    EDITION       VARCHAR(255),
    DOWNLOADABLE  INTEGER NOT NULL
);

CREATE TABLE PRODUCT_HISTORY (
    ID            INTEGER NOT NULL,
    PRODUCT_ID    INTEGER NOT NULL,
    VERSION_HIGH  INTEGER DEFAULT 1 NOT NULL,
    VERSION_LOW   INTEGER DEFAULT 1 NOT NULL,
    EDITION       VARCHAR(255) DEFAULT '' NOT NULL,
    DISCONTINUED  INTEGER DEFAULT 0 NOT NULL,
    DOWNLOADABLE  INTEGER DEFAULT 1 NOT NULL
);

DeerBear wrote:

The reason for that is that this product

will also be doing quite a bit of OLAP and that

usually means your database has to be de-normalized.

Mycroft Holmes wrote:

Most OLAP solutions use a different database than the production DB and the data is denormalised periodically (daily) into said OLAP Db.

CREATE TABLE OLAP_PROD_HISTORY(
    ID            INTEGER NOT NULL,
    PRODUCT_ID    INTEGER NOT NULL,
    VERSION_HIGH  INTEGER DEFAULT 1 NOT NULL,
    VERSION_LOW   INTEGER DEFAULT 1 NOT NULL,
    EDITION       VARCHAR(255) DEFAULT '' NOT NULL,
    DISCONTINUED  INTEGER DEFAULT 0 NOT NULL,
    DOWNLOADABLE  INTEGER DEFAULT 1 NOT NULL,
    CHANGE_DATE   DATE NOT NULL,
    CHANGE_TYPE   INTEGER NOT NULL DEFAULT 0
)

jschell wrote:

How many entries entries based on actual business cases (not developer fantasy) will it contain