Database Schema

Version 2

TABLE category (
    catid        INTEGER PRIMARY KEY AUTOINCREMENT
                         UNIQUE
                         NOT NULL,
    categoryname STRING  UNIQUE
                         NOT NULL,
    description  STRING
)

TABLE subcategory ( subcatid INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, categoryname STRING REFERENCES category (categoryname) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL, subcategoryname STRING NOT NULL, description STRING )

TABLE timeentry ( entryid INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, day DATE NOT NULL, hours DOUBLE NOT NULL, categoryname STRING NOT NULL REFERENCES category (categoryname) ON DELETE CASCADE ON UPDATE CASCADE,

comments STRING,

subcategoryname STRING, FOREIGN KEY(categoryname, subcategoryname) REFERENCES subcategory(categoryname, subcategoryname) ON DELETE CASCADE ON UPDATE CASCADE )

TABLE standardtext ( stdtxtid INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, code STRING NOT NULL UNIQUE, longtext STRING NOT NULL )

TABLE dbVersionInfo ( version INTEGER NOT NULL, upgradedon DATETIME )

UNIQUE INDEX subcategoryNDX on subcategory (categoryname, subcategoryname)

Or see the Version 1 schema.

Back to Home