[ts-gen] Avoiding the enumeration of product/route pairs

[Bill Pippin]

Sam,

You ask about the history detail records written to the table
HistoryBar:

> If I'm correct, the historical bars are printed in the ShimText file,
> with some extra information, and filled into the table HistoryBar.

> But in this table, the field sym doesn't match the cid, right ?
> Is there a way to match these two ?  I'm sorry, I'm lost with these
> cid, ibid, etc, etc. ...

In order to understand the database structure, it helps to focus
on the foreign key references, such as one of the attributes you
mentioned, cid, named for the contract id.

To see how they tie tables together, you may like to begin with
the files syms.ps and xact.ps, which give the foreign key
dependency graphs for the symbology and order records.

They are produced by the script bin/fkey.rb, which requires that
you have the graphviz package installed.  In the recent releases,
although the script is included, the outputs were not; I'll add
syms.ps and xact.ps to future releases, for those lacking graphviz,
and have included them as attachments in this post as well.

Returning to your question, and the foreign key attributes that
are the essence of the answer, note that they are always declared
as foreign key attributes in the create table statement; the
create table statements are found in various instances of files
matching sql/????.sql and source'd by create.sql.  So, first,
starting with the create table statement for Contract:

create table Contract
(
    uid         int unsigned not null auto_increment primary key,
    tag         int unsigned not null references     Product(uid),
    route       int unsigned not null references    Exchange(uid),
                                           unique key(tag, route),

    foreign key (tag)   references    Product(uid),
    foreign key (route) references   Exchange(uid)

)   engine = InnoDb;

Looking at the foreign key declarations, we see that a Contract,
then, is a Product/Exchange pair.  Going on to look at HistoryBar,
we see that the Product/Exchange keys are spelled out in place of a
Contract key:

create table HistoryBar
(
    uid         int unsigned not null auto_increment primary key,
    sym         int unsigned not null references     Product(uid),
    ecn         int unsigned not null references    Exchange(uid),
    bid         int unsigned not null references  HistoryTag(uid),
    time        datetime      not null, unique key(sym, ecn, bid, time),
    open        decimal(10,4) not null,
    high        decimal(10,4) not null,
    low         decimal(10,4) not null,
    close       decimal(10,4) not null,
    vol         int           not null,         --      -1   for NA
    wap         decimal(10,4) not null,         --      -1.0  "  "
    has_gaps    bool          not null,

    foreign key (sym)   references    Product(uid),
    foreign key (ecn)   references   Exchange(uid),
    foreign key (bid)   references HistoryTag(uid)

)   engine = InnoDB;

The use of Product/Exchange pairs reflects the fact that the
Contract table is being de-emphasized, and may be deprecated and
eventually eliminated.  There are just too many possible contracts,
most of which would be routed to obscure exchanges, and so it's
better to work with the Product/Exchange pairs rather than storing
all the possible contracts in the database.  This is particularly
significant for options, of which there are far too many specific
products to start with, before considering all the possible routes.
 
Thanks,

Bill

-------------- next part --------------
A non-text attachment was scrubbed...
Name: syms.ps
Type: application/ps
Size: 7368 bytes
Desc: syms.ps
Url : http://www.trading-shim.org/pipermail/ts-general/attachments/20080617/03c64780/attachment.bin 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: xact.ps
Type: application/ps
Size: 9984 bytes
Desc: xact.ps
Url : http://www.trading-shim.org/pipermail/ts-general/attachments/20080617/03c64780/attachment-0001.bin