--- normative: no --- # SQLite database The complete contents of the Tariff database is available as an [SQLite database](https://sqlite.org/index.html) download from [data.gov.uk](https://www.data.gov.uk/dataset/3bee9a8a-e69c-400e-add5-3345a87a8e25/tariffs-to-trade-with-the-uk-from-1-january-2021). 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](Data-structures;-Transactions.md). The `partition` identifies the [transaction stream](Data-structures;-Transactions.md#transaction-streams) 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. ```dbml enum UpdateType { Updated [note: "1"] Deleted [note: "2"] Created [note: "3"] } Table "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] } Table "Record" { trackedmodel_ptr_id int [pk, not null] "..." "..." } Ref: "Record".trackedmodel_ptr_id > "Tracked Models".id Table "Versions" { id int [pk, not null] created_at datetime [not null] updated_at datetime [not null] current_version_id int } Ref: "Versions".current_version_id > "Tracked Models".id Ref: "Tracked Models".version_group_id > "Versions".id enum Partition { "Seed File" [note: "1"] "Revision" [note: "2"] "Draft" [note: "3"] } Table "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] } Ref: "Tracked Models".transaction_id > "Transactions".id ``` ## 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](Data-structures;-Regulations.md): 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](Data-structures;-Description-records.md): 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.