ESI: Data Structures

Introduction
Transaction Structure
- Detailed Model
  - Register Information
  - Association Information
- Generalized Model
Positions and Balances

Introduction

The Data Structures component of the Framework constitutes a well-defined model for recording securities transactions and other events, and for reflecting their effect in the books and records.

The Data Structures divide naturally into two components: the transaction structure and the positions and balances.

Transaction Structure

The description given in the Framework highlights two entities that are fundamental repositories for our "events of consequence":

Register (aka "Transaction Register")

This entity (table) has the "consequences", which is also the information for updating the positions and balances. Each entry consists of two types of data:

Indicative data (trade date, settlement date, and effective date are the only fields required), security, price, trading account, and counterparty.
Distribution information for quantity and monetary values. There is typically one quantity value (e.g., amount of a security purchased). There may be any number of monetary values (e.g., principal, interest, fees, commission). All distributions are self-balancing.

Contracts may be represented by one row, e.g., a Buy or a Sell. Loan-type agreements, such as repos, will be represented by two: one for the opening and one for the close. A Receive or a Deliver will be one row.

Transaction operations may result in a number of Register entries. For example, a correction adds two new entries, the reversal and the new value.

Association (aka "Relationship", "Keyoff", "Linkage")

This entity (table) links together the Register entries associated with a particular operation. We saw a couple of examples above. In the correction event of our Framework example the Association links together three Register entries: the original entry, its reversal, and the new value. The settlement Association will link the final entry for the Buy with the Receive.

Note that a given Register entry can be part of any number of Associations. In our example the corrected Buy record is linked to both the correction and to the settlement.

The next two sections present views of this structure that differ in level of generality. We will use the one most suitable to the the discussion at hand, which will normally be fairly generalized in terms of modeling level.

Both models show the major entities, their primary keys, and the relationships among the entities. Sample rows for the Framework example (a corrected Buy, settling via a Receive) are also shown.

Detailed Model

This data model presents the Data Structures at the most normalized level we will take them. The Register information is on the left and the Association is on the right.

Detailed Data Structures model

Register Information

This information is on the left of the Data Structures diagram.

The entity TxRegister (for "TransactionRegister") has the indicative information we mentioned above. The two logical securities transactions each have a unique identifier assigned by the system: 12 for the Buy and 39 for the Receive. The history of a transaction is distinguished by a second-level system-assigned sequence number. The original Buy is 12-1, the reversal is 12-2, and the corrected value has 12-3.

The Transaction entity captures this. Because every field on a transaction can be changed (you could correct a Buy of T-Bill into a Sell of an equity, for instance), there is no information other than the primary key to be stored on the table. It is certainly questionable as to whether you would want to implement such a table.

The TxRegDtl ("Transaction Register Detail") entity has the distribution for the Register entry. The original original Buy has two distribution rows, one for the quantity and one for the money. The primary key is generated by adding a system-assigned sequence number to the Register entry. Thus the the quantity distribution entry for the original Buy is 12-1-1 and the money distribution is 12-1-2.

The AmountType is used to distinguish quantity from money distributions. The next field, BookType, distinguishes the books and records figures from memo and off-balance sheet distributions. Almost all of our discussions will be of the official books and records, so the BookType will sometimes be omitted.

There can be more than one quantity or money distribution. We will see transactions with interest later, so we will have two money distributions. It is also possible to omit either the quantity or money distribution. A wire has money only, no quantity, for instance.

Association Information

This information is on the right of the Data Structures diagram.

There are three operations reflected in the Association, with system-assigned keys: 72 for the initial entry of the Buy, 103 for the correction, and 186 for the settlement.

The Entry of the Buy

The Association for the add has two detailed items. The first part of their keys, 72-12-1, indicates that operation entry 72 is associated with Register entry 12-1 (the first history record of the Buy transaction number 12). The next two fields of the key, Q-PRIM, indicate that it a quantity Association of the firm's books and records. The only non-key field, the amount, indicates that none of the Buy's quantity is being offset.

The second detail row is for the principal on the trade. There is again no offset.

The Correction

In the most common situation, the processing of the correction would result in the generation of Register rows 12-2 (the reversal) and 12-3 (the new values for the Buy), and all of the Association rows.

The correction (number 103) is associated with the three Register history rows of the Buy (number 12). The Association for history record 1 shows the full amount of quantity and money being keyed off. This defines what it means for an open item item to be closed.

The Association rows for history record 2 (the reversal) also show the amounts being keyed off, closing this Register record as well.

The final history row, number 3, has no amounts keyed off, so it is open (this is precisely parallel to the Association entry for the original add, when a new, open Buy was first created).

Finally, notice that the amounts of the Association, like the distributions of the Register entries, are also self-balancing for both quantity and money. This will always be the case.

Three-row representations of corrections aren't uncommon. For a discussion from the business point of view see [AfterTrade86], pp. 293-294.

Settlement

It is common to generate the Receive Register entry as part of the settlement operation. The Association entries are then generated as well. This is not a requirement, however. If you actually obtain the Receive entries independently, there were would be an additional ADD Association for the Receive, with both quantity and money amounts zero (similar to the way the entry of the Buy was processed).

The settlement Association, number 186, is linked to the currently open Buy Register record (number 12-1) and to the Receive (number 39-1). The full amounts are keyed off on both, representing the fact that both the Receive and the Buy are now closed.

Again, the Association itself is self-balancing.

Generalized Model

In this version of the Data Structures the Register detail information is merged with the Register information to create one long row. While denormalized, it often makes it easier to illustrate an example. As most of our transactions will have no more than two monetary fields, we will typically display one quantity and two money distributions on the row.

The other difference is that the Association information is also condensed. This is more natural than for the Register information because the amount fields are at the AmountType/BookType level, not the individual distribution. Thus most Associations have one quantity and one money field.

Mid-level Data Structures model

In certain instances we may combine the TxRegAssoc and TRAssocItem entries into one elongated row, further denormalizing the presentation.

Positions and Balances

Positions and balances are, of course, derived data. For the purposes of this discussion, their design is not nearly as important as the transaction-level information above.

From a purely systems point of view, however, how you manage securities positions and balances has a tremendous impact on the overall quality of the system. The following data model is the one we will use where a discussion of this area is required.

Positions and Balances Data Structures model

Some points to note:

The models display three snapshots of the PandB data, on 6/01, 6/02 (6/03 will be exactly the same), and 6/04.
The PandB amounts are shown in thousands, to save space.
The PandB values are updated when the Register entry is written. The first set of example rows in the model displays the table contents after the Buy is entered. At that time:
- The trade date money and quantity amounts are updated for 6/01, and then flowed forward to the end of the month.
- The settlement date amounts for 6/04 are updated, even though that's three days in the future, and flowed to the end of the month.
- The updates for that entry are complete. There will be no additional updates, even when settlement date approaches.
When the Buy is corrected on 6/02 the trade date amounts for 6/02 are updated (and flowed), not 6/01. See Date Bases for additional discussion on dates and date bases in securities accounting.

See Processing Rules for more information on how Positions and Balances are managed within the Framework.

Contents

Key Links