HR wrote << ... The previous database design was - multiple yearly databases - three tables - single key having symb and date combined - no indexing
The new database will be [1] One single database file : OneBigDB.fdb [2] One single table : DAILYBLOB [3] Five columns : SYMB, YMD, TB, MB, DB [4] Primary keys : SYMB, YMD [5] Index : YMD (descending) CREATE TABLE DAILYBLOB ( SYMB VARCHAR(20) NOT NULL, YMD DATE NOT NULL, TB BLOB SUBTYPE BINARY, MB BLOB SUBTYPE BINARY, DB BLOB SUBTYPE BINARY, PRIMARY KEY(SYMB, YMD) ); CREATE DESC INDEX YMD_IDX ON DAILYBLOB(YMD); ... >> Maybe two tables will be better? create table DAILYMAIN ( ID integer not null primary key, SYMB varchar(20) not null, YMD date not null ); create table DAILYBLOB ( ID integer not null primary key, TB blob ..., MB blob ..., DB blob ... ); Individual indexes on SYMB and YMD (whatever proves beneficial). These tables are in a one-to-one relationship (on their IDs, use a single generator). Keeping blobs outside of the "main" table might make for more efficient search and retrieval (if you don't always need the blobs). YMMV. -- Aage J.
