Skip to main content

SQLite database

This section is not part of the standard

The content in this section is only included to help explain the standard, provide examples or make recommendations about use.

It does not contain requirements for complying with the standard and is not governed by the formal standards process.

The information may not have been updated to accurately reflect Government policy.

The complete contents of the Tariff database is available as an SQLite database download from data.gov.uk. The database includes all historical data and past transactions.

The database is published by the Department for Business and Trade (DBT). It only includes tariff data authored or managed by DBT. It therefore does not include VAT and excise measures or quota balances which are managed by His Majesty’s Revenue and Customs (HMRC).

The database is published daily whenever new data is available. In certain cases, the database may lag behind live tariff implementation by several days or may be ahead of live tariff implementation by up to a day. For the canonical source of what is live on the border, always consult the CDS download service or the Online Tariff service.

How the database implements the standard

The database includes one table for each record or subrecord type in the standard. Each field in the record becomes a field in the database table.

Three common tables are used to simplify management of versions.

Transactions

The table common_transactions contains one row for every transaction.

The partition identifies the transaction stream that the transaction is part of:

  • 1 specifies that the transaction came from the EU transaction stream
  • 2 specifies that the transaction came from the UK transaction stream

The order field identifies the position of the transaction (starting from 1) within the transaction stream, and corresponds to the “logical” transaction ID for the transaction.

The combination of the partition and order fields on the transactions table specifies the correct ordering of transactions. To query for transactions in the correct order, use:

SELECT * FROM transactions ORDER BY partition ASC, order ASC

Version groups

A ‘version group’ ties together all of the versions of a single record. Each version of the same record links (via the tracked model) to the same version group.

Effectively this means that one version group corresponds to one set of identifying fields per record type.

The current version of a record is the version that appears in the most recent transaction. The version group maintains a cache of this current version when considering all transactions which is available via the current_version_id field.

The table common_version_groups contains one row for each version group.

Tracked models

Each version of each record is identified by a common base called a ‘tracked model’. The tracked model links the record to a version group and a transaction.

The table common_tracked_models contains one row for every tracked model. The primary key on each record table is a foreign key into the tracked models table.

Database Tracked Models Tracked Models id int pk, not null update_type UpdateType not null polymorphic_ctype_id int not null transaction_id int not null version_group_id int not null Versions Versions id int pk, not null created_at datetime not null updated_at datetime not null current_version_id int Tracked Models:e->Versions:w Transactions Transactions id int not null created_at datetime not null updated_at datetime not null import_transaction_id int order int not null composite_key text not null workbasket_id int not null partition Partition not null Tracked Models:e->Transactions:w Record Record trackedmodel_ptr_id int pk, not null … “…” Record:e->Tracked Models:w Versions:e->Tracked Models:w

Querying current data

Querying the database for the current set of records is not as simple as just querying each table because each table contains every version of the records as a separate row.

Instead, the version group contains a current_version_id which identifies the current version of each record. Returning only the current records from an example records table therefore involves using a join onto the version group:

SELECT * FROM records
    INNER JOIN common_version_groups
        ON records.trackedmodel_ptr_id = common_version_groups.current_version_id

Foreign keys on a table link to the version of the foreign record that were current when that record was written. Joining across tables using the foreign key directly may therefore return records from an earlier version. What is normally wanted is the current version of all data returned in the query.

This is achieved by finding the version group for the foreign key as above and then linking back to the foreign record table with the current version:

SELECT * FROM records
    INNER JOIN common_tracked_models
        ON records.foreign_record_id = common_tracked_models.id
    INNER JOIN common_version_groups
        ON common_tracked_models.version_group_id = common_version_groups.id
    INNER JOIN foreign_records
        ON common_version_groups.current_version_id = foreign_records.trackedmodel_ptr_id

Differences from the standard

There are a number of instances where the database does not follow the pattern of one record type being implemented as one table.

  • For regulations: all regulations are contained in a single regulations table and the relationships between them are implemented as link tables. This is instead of each regulation record type living in its own table.
  • For descriptions: because the SQLite database is only designed to support the English language, each description and description period record is contained in the same database table. Non-English descriptions in historical data are not included.