afs:TRADE Database Documentation

Database Documentation

This page explains the organization and relationships of the tables in the afs:TRADE database from a high-level perspective, with links to the details of individual tables.

Lookup tables

Lookup tables contain the choices for values of columns in other tables that are constrained in some way. For example, the value for a ratings code must match something in the list published by the ratings agency. Similarly, industry codes must be standardized for compliance and reporting purposes. If you can't type freeform into a field on a form, it probably has a lookup table behind it.

All afs:TRADE lookup tables fall into one of two categories: Standard or Custom. Standard lookup tables are populated by AFS when the database is installed. They should not be modified without AFS approval, or system failures could result. Custom tables are often pre-populated at installation, but can be modified by customers with the Lookups editor.

Click here for a list of lookup tables. Most lookup tables have the same columns and indexes, so only divergent tables are individually documented.

Products and Issuers

Information about individual securities and their issuers is maintained in the following tables. Every afs:TRADE system has product tables. Issuer profiles, on the other hand, are usually included only in money market systems where new security descriptions are created on the fly.

Product Tables
product	The root table for product information. Primary key is the product_id. Transactions and positions join to this table for a complete description of the security under consideration.
prodrat	Product ratings, 1:1 join to product table on product_id
prodcalc	Pre-calculated financial metrics, such as duration, average life and worst-case yield, 1:1 join to product table on product_id. Used by reports and Web tools that do not have access to the full-fledged financial calculation routines.

Issuer Tables
issuer	The root table for issuer information. Primary key is issuer_id.

Accounts and Transactors

Information about internal accounts (portfolios), contraparties, and transactors in maintained in this collection of tables. Internal accounts and contraparties may be organized into hierarchies for trading and/or reporting purposes.

Internal Account/Portfolio Tables
account	The root table for internal account information. Primary key is acct_code.
acttotal	Extended information about account aggregates and compliance rules, 1:1 join to account table on acct_code (exists only in systems that do compliance checking)
tradedsk	Each account can be traded on one or more desks, each of which defines certain trading rules and lists of authorized transactors. Primary key is desk_code.

Contraparty Tables
opmast	The root table for contraparty information. Primary key is other_party_code.
opdtl	Extended information about a contraparty, 1:1 join to opmast.
opparent	Defines the parent-level contraparty (such as a mutual fund group or a bank trust department), 1:N mapping to a set of opmast records. Primary key is parent_code.
oppardtl	Extended information about a contraparty parent, 1:1 join to opparent.

Transactor Tables
transact	The root table for transactors (traders, salesmen, etc.). Primary key is trans_id.
trandesk	M:N mapping of transactors and desks, determines who is entitled to trade in particular accounts.

Tickets and Transactions

Once products and accounts have been established, transactions can be executed. A transaction is an agreement between an inside account and a contraparty to buy or sell a specific quantity of a particular security at a negotiated price. The afs:TRADE system stores transactions in a 1:N master/detail hierarchy to allow a single transaction to be allocated to multiple accounts (internal or external, depending on the system). Even if a transaction only involves one account, a 1:1 mapping between the master and detail tables is created.

The afs:TRADE system also supports cash-only transactions, which can be allocated but do not use a master/detail relationship to do so.

Transaction Tables
tckmast	The master table for transactions. Primary key is primary_ticket_number + revision. Tickets are never deleted, so it is important only to fetch those where is_canceled = 0.
tckdtl	Allocation details for transactions, 1:N mapping to tckmast.
cashtckt	The root table for cash-only transactions. Primary key is primary_ticket_number + detail_ticket_number.

Positions

A position represents a quantity of a security owned (or owed, if short) by a particular internal account. Positions are created automatically when tickets are posted. In fact, every current position in the afs:TRADE system must be covered by the transaction(s) that created it, or the system is considered to be out of balance. The realized table is responsible for maintaining this mapping.

Some broker-dealer systems also maintain information about customer positions. This table can be updated by the ticket posting process; but unlike internal positions, customer positions also can be imported or entered directly without supporting transactions.

Position Tables
currposn	The root table for portfolio/inventory positions. Primary key is desk_code + acct_code + product_id + type_indicator, which means the same security can be owned more than once in a particular account.
realized	M:N mapping between covering and realizing transactions. Realized columns will have the value "LOT" if the covering ticket is still in position.

Customer Position Tables
custposn	The root table for customer positions. Primary key is acct_code + product_id.

Events

The afs:TRADE system provides a mechanism to keep multiple users synchronized about each others' actions. Rather than messaging directly between computers, the system records every significant action in a database table. Each event is tagged with a date/time, event type, and unique ID. Interested processes then poll this table for recent updates on a regular basis.

Event Tables
afsevent	The root table for recording system events in real time. Primary key is event_number.