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 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.
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.
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.
PositionsA 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.
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.