I have posted a few times and had several replies which made me to change the design of a database. So, I would like to ask advices/comments again because I am a novice in database and not sure if I am doing properly.

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);

The table DAILYBLOB will have
- SYMB will be 'S0001' through 'S3000'
- Initially, 3000 * 365 * 20 = 32,850,0000 (30M approx) records for the past 30 years
- 3000 new records every day
- 1,095,000 records every year (1M approx)
- TB's size is varying and ranges from 0 bytes to 300 kilobytes.
- MB's size is fixed and 20 kilobytes or so.
- DB's size is fixed and 100 bytes or so.

As shown above, the database will have 30M history records as of 2011/12/31. This is 1M records per year.
And from 2012/01/01, new 3000 records will be inserted everyday.
These 3000 insertions will be done all at once and only once a day.

After 10 more years, the database will grow up to contain more than 40M records.

I hope

- FB can easily and efficiently handle 30-40M records or whole lot more in the future.
Having a big continuously growing database instead of yearly(1M records) split multiple databases is a good way to go?
- FB can select a record, for a specific set of symbol+date, fast enough. The most common query for this database is to find a single TB, MB for a specific symbol. For example, TB for S1234 on 2011/07/25. For DB, the query will request a range records for a specific symbol. For example, DBs for S1234 from 2007/1/1 to 2011/12/24. SQL for each query would look like
SELECT TB FROM DAILYBLOB WHERE SYMB='S1234' AND YMD='2011-07-25';
SELECT MB FROM DAILYBLOB WHERE SYMB='S1234' AND YMD='2011-07-25';
SELECT DB FROM DAILYBLOB WHERE SYMB='S1234' AND (YMD>='2007-01-01' AND YMD<='2011-12-24');
- FB can select the latest date for a specific symbol, fast enough.
SELECT FIRST 1 YMD FROM DAILYBLOB WHERE SYMB = 'S1234' ORDER BY YMD DESC;
- FB can insert records for all the missing days fast and efficiently.
After finding the latest date, we can calculate the missing days up to today. Then all the missing records from the latest to yesterday will be inserted to make the database up-to-date. For example, if the latest date was 2011/11/31 and today is 2011/12/24, the database misses records from 2011/12/01 to 2011/12/23. Therefore 3000*23=69000 records will be inserted.
INSERT INTO DAILYBLOB VALUES('S0001', '2011-12-01', <TB>, <MB>, <DB>);
...
INSERT INTO DAILYBLOB VALUES('S3000', '2011-12-01', <TB>, <MB>, <DB>);
...
...
INSERT INTO DAILYBLOB VALUES('S0001', '2011-12-23', <TB>, <MB>, <DB>);
...
INSERT INTO DAILYBLOB VALUES('S3000', '2011-12-23', <TB>, <MB>, <DB>);

I am going to use embedded server on Windows XP and 7 or later.
Any comments/advices would be very appreciated including but not limited to
- the configuration of database such as page size, # of page buffers, cache size, write mode, sweep interval and so on. These things are too advanced for me yet.
- the structure of table
- indexing
- sql statements
- performance
- etc

One more thing to note is that database will be accessed via IBPP api.

thx so much reading a long text.
HR