Reading your various recent posts, I may be wrong, but I think I'm
correct in saying you know very little about databases, Firebird or
otherwise, and that's fine, we all started somewhere.
While people are very willing to help with tips, tricks and solutions to
all sorts of sql problems, they are not here to design and implement
your system.
Why don't you read the documentation - there is a lot, covering every
question you've asked - and then ask your question related to a specific
issue.
The other option would be to take some database courses, on-line or in
person.

I'm not criticising you, just trying to help, because otherwise people
will see a post from you and just delete it without opening it.

Alan J Davies
Aldis

On 24/12/2011 20:29, ehaerim wrote:
>
>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>, Aage Johansen <aagjohan@...> >
> > 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).
>
> 1) I think I need an index on YMD definitely for fast search of the
> latest date.
> 2) I will never query for a multiple symbols but always for a single
> symbol only.
>
> Considering 1) and 2), what indexes do I need to create? Please show me
> appropriate sql statements.
>
> > These tables are in a one-to-one relationship (on their IDs, use a
> > single generator).
> >
> I have never used a generator. After reading LANGREF.PDF,
> CREATE GENERATOR ID_GEN;
> COMMIT;
> SET TERM !! ;
> CREATE TRIGGER CREATE_ID FOR DAILYMAIN
> BEFORE INSERT POSITION 0
> AS BEGIN
> NEW.ID = GEN_ID(ID_GEN, 1);
> END
> SET TERM ; !!
>
> 1) Is this right?
> 2) Create generator for DAILYMAIN only?
> 3) How do I use this 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.
> >
>
> 1) Can you tell me more how efficient by keeping blobs outside of the
> main table?
> 2) As I said in my first post, there are three main retrieval operations:
> - retrieval of the latest date for a specific symbol
> Select statement for this was like (in terms of a single table DAILYBLOB):
> SELECT FIRST 1 YMD FROM DAILYBLOB WHERE SYMB = 'S1234' ORDER BY YMD DESC;
> - retrieval of either TB or MB for a specific symbol and a specific date
> - retrieval of DBs for a specific symbol and a specific date
> Select statements for these were like (in terms of a single table
> DAILYBLOB):
> 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');
>
> How can I change these statements if I use two tables(DAILYMAIN and
> DAILYBLOB) following your suggestion?
>
> 3) insert was like:
> 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 not sure how to change these insert statements using two table
> architecture.
>
> 4) BTW, what's YMMV?
>
> thx again
> HR
>
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com <http://www.avg.com>
> Version: 2012.0.1901 / Virus Database: 2109/4700 - Release Date: 12/24/11
>