Version 1

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,
    upgadedon   DATETIME
)
UNIQUE INDEX subcategoryNDX on subcategory (categoryname, subcategoryname)


Back to Home