Data structures; Regulations - uktrade/tariff-data-manual GitHub Wiki

Every measure entered into the tariff database must be associated with a regulation, i.e. a piece of legeslation.

This is so there's a 'legal base' for all tariff and regulatory activity. Without this, measures cannot be traced back to why they were created.

The need for a regulation is built into the schema for TARIC files. It is a mandatory field for the measure table.

Each regulation may have any number of measures associated with it. Every measure is associated with just one measure-generating regulation. I.e. regulations and measures have a one-to-many relationship.

Base regulations

A base regulation introduces new tariff and commercial measures. They do not originate from, or depend on, other regulations. Unless they are suspended, base regulations are valid from their entry into force to their end, or they can be open-ended.

Measures under these regulations have the same validity, unless they are given explicit, earlier end-dates.

In the UK Tariff

The UK Tariff from 2021 onwards only uses open-ended base regulations, and always specifies end dates on measures explicitly. It does not use other types of regulations (e.g. modification regulations as found in the EU Tariff).

Historic data (i.e. measures dating back to before 2021) still includes other types of regulations and data structures found in the EU tariff, thus these fields and variables are still found in the data, but they are not used for new measures or regulations under the post-2021 UK Tariff.

Regulation IDs

Each regulation ID consists of a single letter prefix (listed in the table below) and a seven digit number. Of this number the first two digits refer to the year of the regulation (i.e. 23 for 2023), the next four a sequence number (padded with leading zeros), and the final digit a part value, allowing the same regulation to be entered into the system several times if necessary (e.g. if each instance references a specific part of the legeslation.

Note that the current post-2021 UK tariff regulations use a subtly different set of prefixes to the pre-2021 EU tariff regulations. As the latter still appear in historic regulations, both sets are listed below for completeness.

UK tariff prefixes

Prefix	Description
C	Draft regulation
P	Preferential trade agreement/FTA
U	Unilateral preferences (GSP)
S	Suspensions and reliefs
X	Import and export control
N	Trade Remedies
M	MFN
Q	Quotas
A	Agri measures

EU tariff prefixes

Prefix	Description
C	Draft regulations, decisions and agreements
R	Regulations
D	Decisions
A	Agreements
I	Information (this is not a legal act but it is used for information)
J	Judgement of the European Court of Justice

Grouping regulations

Every base regulation refers to a single regulation group.

Regulation groups categorise regulations according to their purpose. They also control what preference codes will be able to use the measure. If regulations are put into the wrong group, associated measures may not be usable by traders.

SELECT regulation_groups.group_id AS "Group",
  regulation_groups.description AS "Description",
  COUNT(regulations.regulation_id) AS "No of regulations in group",
  MAX(regulations.validity_start) AS "Last used"
FROM regulations
   INNER JOIN common_version_groups as cvg0
       ON regulations.trackedmodel_ptr_id = cvg0.current_version_id
   INNER JOIN common_tracked_models
       ON regulations.regulation_group_id = common_tracked_models.id
   INNER JOIN common_version_groups AS cvg1
       ON common_tracked_models.version_group_id = cvg1.id
   INNER JOIN regulation_groups
       ON cvg1.current_version_id = regulation_groups.trackedmodel_ptr_id
GROUP BY regulations.regulation_group_id
ORDER BY regulation_groups.group_id

The list of regulation groups is rarely amended, and is only done with the agreement of all stakeholders as it would require considerable downstream changes.

Draft regulations

Sometimes it is necessary to send new policy to border systems before the legislation has been accepted by Parliament. In this case, a "draft regulation" is used.

Draft regulations are identified by the prefix C in their regulation ID.

Draft regulations are useful for measures where the start date has not yet been decided but it's necessary to start data entry, or to get measures onto systems whilst legislation is being laid. Draft regulations may replaced at a later date when the legislation has been laid, however in many cases there is no value in replacing the regulation in the data and regulations with the prefix 'C' may no longer be draft regulations. Use the approved flag to identify if a regulation is still draft or not.

Sending draft regulations before they're approved

A draft regulation can be sent with a 'not approved' flag. Unapproved draft regulations are unique in that the measures that are associated with them do not take effect. They have no executive power and will not show up as active measures in the border systems or in the UK tariff.

When the legislation is signed off by Parliament the regulation can be given an 'approved' flag. At this point, all the measures become live.

The following 2 rules apply:

1. Only draft regulations can be 'not approved'. Other types of regulations must always be 'approved'.
2. The approved flag can only be changed from 'not approved' to 'approved'. Once a regulation has been approved, it cannot be changed back.

Data structure (UK tariff)

Under the post-2021 UK Tariff, the data strucutre for regulations consists of three entities "Regulations", "Regulation Groups" and "Regulation Group Descriptions", as shown in the entity-relationship diagram below. The latter two tables are straightforward, but the "Regulations" table is a little more complex. Firstly note that there are several fields in the table not shown here; these are associated with the EU tariff structure, are not used under the UK tariff, and only apply to historic data (details of the historic data structure are further down the page).

Note also that the "Information Text" field consists of three pipe delimited variables, the title of the regulation, it's public identifier, and a url. For example, regulation 'P2102410' has the title 'The Customs Tariff (Preferential Trade Arrangements) (EU Exit) (Amendment) Regulations 2021', the public identifier S.I. 2021/241, and the url https://www.legislation.gov.uk/uksi/2021/241. The title and identifier are taken direct from the legeslation itself (as can be seen by following the url).

There are some subtle differences between the structure of the XML data (detailed here) and the structure of the RDBMS structure. Notably that in the RDBMS the regulation group description is contained in the "Regulation Group" table (not spearated out into its own table), and that the "Information Text" field is seaparted out into three fields, rather than being three variables in one field as detailed above.

Entity-relationship diagram for the UK tariff regulations

Table "Regulations" {
  "Regulation Id" char(8) [pk]
  "Regulation Group Id" char(3) [pk]
  "Published at" date
  "Information Text" varchar(255)
  "Approved Flag" binary
  "Stopped Flag" binary
  "Validity Start" date [not null]
  "Validity End" date
}

Table "Regulation Group" {
  "Group Id" char(3) [pk]
  "Validity Start" date [not null]
  "Validity End" date
}

Table "Regulation Group Description" {
  "Group Id" char(3) [pk]
  "Language Id" char(2)
  "Description" varchar(255)
}

Table "Measure" {
  "Measure Id" int [pk]
  "Measure Generating Regulation Id" char(8) [pk]
  "Justification Regulation Id" char(8) [pk]
  "..." "..."
}

Ref: "Regulations"."Regulation Group Id" > "Regulation Group"."Group Id"
Ref: "Regulation Group Description"."Group Id" > "Regulation Group"."Group Id"
Ref: "Measure"."Measure Generating Regulation Id" > "Regulations"."Regulation Id"
Ref: "Measure"."Justification Regulation Id" > "Regulations"."Regulation Id"

Data structure (historic EU tariff data)

The historic EU tariff data uses a considerably more complex data structure than the post-2021 UK tariff data structure. The main difference is that regulations are split into roles, there is base regulation (which is what all new UK tariff regulation falls under) and seven other types of regulation role (provisional/definitive antidumping, modification, porogation, complete/explicit abrogation, full temporary stop).

With the excption of the base and the two antidumping regulation types, each role type has its own table detailing the legeslation that falls uner that role type and which is linked to the main "Regulations" table.

Porogation and modification regulation also has associated 'Action' tables, that indicate the regulation id that is being poroged/modified. There is also a "Replacement Regulation" table that associates regulations with the regulations that they are replacing.

Note that as above, the structure detailed here is for the XML data. In the RDBMS the structure is simplified, the main "Regulations" table is mostly the same, as with the UK tariff strucutre the group descriptions are included in the "Regulation Groups" table. There are then three tables for amendments replacements and suspensions, which detils which regulation is doing the amendment/replacement/supension, and which regulation is being amended/replaced/suspended.

Entity-relationship diagram for historic EU tariff regulations

Table "Regulations" {
  "Regulation Id" char(8) [pk]
  "Regulation Group Id" char(3) [pk]
  "Published at" date
  "Information Text" varchar(255)
  "Approved Flag" binary
  "Stopped Flag" binary
  "Validity Start" date [not null]
  "Validity End" date
  "Regulation Role Type Id" char(1) [pk]
  "Official Journal Number" varchar(10)
  "Official Journal Page" int
  "Community Code" int
  "Replacement Indicator" binary
  "Effective End Date" date
  "Related Antidumping Regulation Role" char(1) [pk]
  "Related Antidumping Regulation Id" char(8) [pk]
  "Complete Abrogation Regulation Role" char(1) [pk]
  "Complete Abrogation Regulation Id" char(8) [pk]
  "Exlpicit Abrogation Regulation Role" char(1) [pk]
  "Exlpicit Abrogation Regulation Id" char(8) [pk]
}

Table "Regulation Group" {
  "Group Id" char(3) [pk]
  "Validity Start" date [not null]
  "Validity End" date
}

Table "Regulation Group Description" {
  "Group Id" char(3) [pk]
  "Language Id" char(2)
  "Description" varchar(255)
}

Table "Measure" {
  "Measure Id" int [pk]
  "Measure Generating Regulation Id" char(8) [pk]
  "Measure Generating Regulation Role Id" char(1) [pk]
  "Justification Regulation Id" char(8) [pk]
  "Justification Regulation Role Id" char(1) [pk]
  "..." "..."
}

Table "Regulation Role Type" {
  "Regulation Role Type Id" char(1) [pk]
  "Validity Start" date [not null]
  "Validity End" date
}

Table "Regulation Role Type Description" {
  "Regulation Role Type Id" char(1) [pk]
  "Language Id" char(2)
  "Description" varchar(255)
}

Table "Regulation Replacement" {
  "Replacing Regulation Id" char(8) [pk]
  "Replacing Regulation Role" char(1) [pk]
  "Replaced Regulation Id" char(8) [pk]
  "Replaced Regulation Role" char(1) [pk]
  "Measure Type Id" int
  "Geographical Area Id" int
  "Chapter Heading" int
}

Table "Complete Abrogation Regulation" {
  "Complete Abrogation Regulation Id" char(8) [pk]
  "Complete Abrogation Regulation Role" char(1) [pk]
  "Published at" date
  "Official Journal Number" varchar(10)
  "Official Journal Page" int
  "Replacement Indicator" binary
  "Information Text" varchar(255)
  "Approved Flag" binary
}

Table "Explicit Abrogation Regulation" {
  "Explicit Abrogation Regulation Id" char(8) [pk]
  "Explicit Abrogation Regulation Role" char(1) [pk]
  "Published at" date
  "Official Journal Number" varchar(10)
  "Official Journal Page" int
  "Replacement Indicator" binary
  "Information Text" varchar(255)
  "Approved Flag" binary
}

Table "Full Temporary Stop Regulation" {
  "Full Temporary Stop Regulation Id" char(8) [pk]
  "Full Temporary Stop Regulation Role" char(1) [pk]
  "Published at" date
  "Information Text" varchar(255)
  "Approved Flag" binary
  "Stopped Flag" binary
  "Validity Start" date [not null]
  "Validity End" date
  "Regulation Role Type Id" char(1)
  "Official Journal Number" varchar(10)
  "Official Journal Page" int
  "Community Code" int
  "Replacement Indicator" binary
  "Effective End Date" date
  "Complete Abrogation Regulation Role" char(1) [pk]
  "Complete Abrogation Regulation Id" char(8) [pk]
  "Exlpicit Abrogation Regulation Role" char(1) [pk]
  "Exlpicit Abrogation Regulation Id" char(8) [pk]
}

Table "FTS Regulation Action" {
  "FTS Regualtion Id" char(8) [pk]
  "FTS Regulation Role" char(1) [pk]
  "Stopped Regulation Id" char(8) [pk]
  "Stopped Regulation Role" char(1) [pk]
}

Table "Measure Partial Temporary Stop" {
  "Measure Id" int [pk]
  "Validity Start" date [not null]
  "Validity End" date
  "Partial Temporary Stop Regulation Id" char(8) [pk]
  "Partial Temporary Stop Regulation Official Journal Number" varchar(10)
  "Partial Temporary Stop Regulation Official Journal Page" int
  "Abrogation Regulation Id" char(8) [pk]
  "Abrogation Regulation Official Journal Number" varchar(10)
  "Abrogation Regulation Official Journal Page" int
}

Table "Modification Regulation" {
  "Modification Regulation Id" char(8) [pk]
  "Modification Regulation Role" char(1) [pk]
  "Published at" date
  "Official Journal Number" varchar(10)
  "Official Journal Page" int
  "Validity Start" date [not null]
  "Validity End" date
  "Effective End Date" date
  "Base Regulation Id" char(8) [pk]
  "Base Regulation Role" char(1) [pk]
  "Complete Abrogation Regulation Role" char(1) [pk]
  "Complete Abrogation Regulation Id" char(8) [pk]
  "Exlpicit Abrogation Regulation Role" char(1) [pk]
  "Exlpicit Abrogation Regulation Id" char(8) [pk]
  "Replacement Indicator" binary
  "Stopped Flag" binary
  "Information Text" varchar(255)
  "Approved Flag" binary
}

Table "Prorogation Regulation" {
  "Prorogation Regulation Id" char(8) [pk]
  "Prorogation Regulation Role" char(1) [pk]
  "Published at" date
  "Official Journal Number" varchar(10)
  "Official Journal Page" int
  "Replacement Indicator" binary
  "Information Text" varchar(255)
  "Approved Flag" binary
}

Table "Prorogation Regulation Action" {
  "Prorogation Regulation Id" char(8) [pk]
  "Prorogation Regulation Role" char(1) [pk]
  "Prorogated Regulation Id" char(8) [pk]
  "Prorogated Regulation Role" char(1) [pk]
  "Prorogated Date" date
}

Ref: "Regulations"."Regulation Group Id" > "Regulation Group"."Group Id"
Ref: "Regulations"."Regulation Role Type Id" > "Regulation Role Type"."Regulation Role Type Id"
Ref: "Regulations"."Complete Abrogation Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"
Ref: "Regulations"."Complete Abrogation Regulation Id" > "Complete Abrogation Regulation"."Complete Abrogation Regulation Id"
Ref: "Regulations"."Exlpicit Abrogation Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"
Ref: "Regulations"."Exlpicit Abrogation Regulation Id" > "Explicit Abrogation Regulation"."Explicit Abrogation Regulation Id"

Ref: "Regulation Group Description"."Group Id" > "Regulation Group"."Group Id"

Ref: "Measure"."Measure Generating Regulation Id" > "Regulations"."Regulation Id"
Ref: "Measure"."Measure Generating Regulation Role Id" > "Regulation Role Type"."Regulation Role Type Id"
Ref: "Measure"."Justification Regulation Id" > "Regulations"."Regulation Id"
Ref: "Measure"."Justification Regulation Role Id" > "Regulation Role Type"."Regulation Role Type Id"

Ref: "Regulation Role Type Description"."Regulation Role Type Id" > "Regulation Role Type"."Regulation Role Type Id"

Ref: "Regulation Replacement"."Replacing Regulation Id" > "Regulations"."Regulation Id"
Ref: "Regulation Replacement"."Replacing Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"
Ref: "Regulation Replacement"."Replaced Regulation Id" > "Regulations"."Regulation Id"
Ref: "Regulation Replacement"."Replaced Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"

Ref: "Complete Abrogation Regulation"."Complete Abrogation Regulation Id" > "Regulations"."Regulation Id"
Ref: "Complete Abrogation Regulation"."Complete Abrogation Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"

Ref: "Explicit Abrogation Regulation"."Explicit Abrogation Regulation Id" > "Regulations"."Regulation Id"
Ref: "Explicit Abrogation Regulation"."Explicit Abrogation Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"

Ref: "Full Temporary Stop Regulation"."Full Temporary Stop Regulation Id" > "Regulations"."Regulation Id"
Ref: "Full Temporary Stop Regulation"."Full Temporary Stop Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"
Ref: "Full Temporary Stop Regulation"."Complete Abrogation Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"
Ref: "Full Temporary Stop Regulation"."Complete Abrogation Regulation Id" > "Regulations"."Regulation Id"
Ref: "Full Temporary Stop Regulation"."Exlpicit Abrogation Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"
Ref: "Full Temporary Stop Regulation"."Exlpicit Abrogation Regulation Id" > "Regulations"."Regulation Id"

Ref: "FTS Regulation Action"."FTS Regualtion Id" > "Full Temporary Stop Regulation"."Full Temporary Stop Regulation Id"
Ref: "FTS Regulation Action"."FTS Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"
Ref: "FTS Regulation Action"."Stopped Regulation Id" > "Regulations"."Regulation Id"
Ref: "FTS Regulation Action"."Stopped Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"

Ref: "Measure Partial Temporary Stop"."Partial Temporary Stop Regulation Id" > "Regulations"."Regulation Id"
Ref: "Measure Partial Temporary Stop"."Abrogation Regulation Id" > "Regulations"."Regulation Id"

Ref: "Modification Regulation"."Modification Regulation Id" > "Regulations"."Regulation Id"
Ref: "Modification Regulation"."Modification Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"
Ref: "Modification Regulation"."Base Regulation Id" > "Regulations"."Regulation Id"
Ref: "Modification Regulation"."Base Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"
Ref: "Modification Regulation"."Complete Abrogation Regulation Role" > "Complete Abrogation Regulation"."Complete Abrogation Regulation Role"
Ref: "Modification Regulation"."Complete Abrogation Regulation Id" > "Complete Abrogation Regulation"."Complete Abrogation Regulation Id"
Ref: "Modification Regulation"."Exlpicit Abrogation Regulation Role" > "Explicit Abrogation Regulation"."Explicit Abrogation Regulation Role"
Ref: "Modification Regulation"."Exlpicit Abrogation Regulation Id" > "Explicit Abrogation Regulation"."Explicit Abrogation Regulation Id"

Ref: "Prorogation Regulation"."Prorogation Regulation Id" > "Regulations"."Regulation Id"
Ref: "Prorogation Regulation"."Prorogation Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"

Ref: "Prorogation Regulation Action"."Prorogation Regulation Id" > "Prorogation Regulation"."Prorogation Regulation Id"
Ref: "Prorogation Regulation Action"."Prorogation Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"
Ref: "Prorogation Regulation Action"."Prorogated Regulation Id" > "Regulations"."Regulation Id"
Ref: "Prorogation Regulation Action"."Prorogated Regulation Role" > "Regulation Role Type"."Regulation Role Type Id"

Validation rules

Code	Description
ROIMB1	Uniqueness rule for the combination of regulation id and role id.
ROIMB4	Mandatory subrecord rule that a referenced regulation group must exist.
ROIMB44	The “Regulation Approved Flag” indicates for a draft regulation whether the draft is approved, i.e. the regulation is definitive apart from its publication (only the definitive regulation id and the O.J. reference are not yet known). A draft regulation (regulation id starts with a ‘C’) can have its “Regulation Approved Flag” set to 0=’Not Approved’ or 1=’Approved’. Its flag can only change from 0=’Not Approved’ to 1=’Approved’. Any other regulation must have its “Regulation Approved Flag” set to 1=’Approved’.
ROIMB46	Deletion while in use rule for regulations that are used as a justification regulation in a measure. Except for ‘C’ regulations used only in measures as both measure-generating regulation and justification regulation.
ROIMB47	Validity contained rule requiring regulation group validity to span the period of the base regulation.
ROIMB8	Validity contained rule requiring eplicit dates of related measures to be within the validity period of the base regulation.

For more information see the system documentation. Note that these validation rules are only for the post-2021 UK tariff.