Database

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

SELECT DateAdd(hour, 9, DateDiff(dd, 0, YourDateTimeColumn))

SELECT DateAdd(hour, 9, Convert(datetime2(0), YourDateColumn))