The Office of Foreign Assets Control (OFAC) maintains its sanctions lists in a variety of machine-readable formats. These formats can be imported into a variety of software programs. However, certain files were specifically designed for importation into relational databases. When using OFAC's delimited or fixed width files, it is important that the user recognize that in order to view a complete sanctions list they must download all four file types in a series and link them together in a relational database. This will allow the user to produce a complete copy of the list for screening purposes and for export. The reason for this design is that OFAC's sanctions lists contain numerous one to many relationships. In other words, every primary Specially Designated Nationals (SDN) List name may also have many addresses and many aliases associated with it.
Relational databases are typically made up of multiple tables that are related to each other through index columns known as "key columns" Each table in a relational database will typically have a "primary key column" made up of unique index numbers that are not repeated (or reused in the event of a record's deletion). For example, in the SDN.CSV file the primary key column (known as the ENT_NUM column) is the first column in the delimited file. This column links to equivalent versions of the ENT_NUM columns in the ADD.CSV file and the ALT.CSV file. These key columns in the ADD.CSV and ALT.CSV columns are called "foreign keys". Foreign key columns have unique record numbers that correspond to the numbers in the primary key column in the SDN.CSV file. Foreign key columns have integers that may repeat (unlike a primary key column which cannot have integers repeat). This allows for one primary SDN name to be associated with several addresses and/or several aliases. For example primary SDN record "117" is associated with all addresses that have a corresponding integer of 117 in the ENT_NUM column of the ADD.CSV file. Relationships among tables are established by linking these primary key and foreign key columns together using the tools available in the relational database software package one is using. Since the column names are not stored in the actual sanctions list data files, users should read the data specification for these files . The specification also explains how each file is structured as well as what the field widths and data types are for each column.
Below is an example of a simple relational database built in Microsoft Access using the four SDN CSV flat files. The column names have been added using the information in the aforementioned data specification. This tutorial has been created to help users understand the importance of downloading all of the files associated with a given sanctions list file series. If users only download one of the files (for example SDN.CSV) they will be missing large amounts of list data and may be at greater risk of doing business with an SDN or other sanctioned entity.
The image above shows that the primary SDN.CSV file has one to many relationships with the ADD.CSV (address) file and the ALT.CSV (alias) file. The small key symbol next to the columns in each table denotes which columns represent that table's primary key. Foreign key columns do not have a symbol next to them, but are linked back to the SDN.CSV table and have the same column name. The SDN_COMMENTS.CSV (remarks field spill over) file contains additional remarks field data that exceeds the 1000 character limit of the remarks column in the SDN.CSV file. The SDN_COMMENTS file (and other files like it for OFAC's other sanctions lists) was created in August 2013. Users should be downloading this file as well or they risk missing identifiers that are typically stored in the remarks field. There is a one to one relationship between the SDN_COMMENTS data and the primary SDN data since a primary SDN record can only have one spill over remarks field.