Introduction
My first SQL project focuses on my trade analyst experience, specifically in assigning tariff codes to customer products. These codes, crucial for determining duty rates, often require intricate analysis of technical and trade data to ensure accuracy. While the classification can apply to any items, this project is limited to electronic products.
Database structure
The 'TARIFF_CLASSIFICATION' database stores information on customer products requiring customs duty rates. It features a primary 'CLASSIFICATION' table detailing product ID, category, tariff codes, customer and classifier IDs (Person), delivery and classification dates, information about loaded data to external systems and process dates.
Additional tables provide in-depth data and include for example, table 'RECLASSIFICATION' for reclassified products, with 'CHANGE_CLASSIFICATION' holding codes and reasons for such changes.
Database diagram
Below is a diagram of all tables in the database and their connections with each other.
Practical application
Below is a description of ideas on how to use such a database in practice.
Views
- Quick view of the classification (product id, product category and assigned tariff code) for the individual customer, for example Luxens. A view was called 'TariffClassForLuxens'.
The raw code and result of script is following:
- Another interesting example is a view of the most frequently classified customer.
The raw code and result of script is following:
- The last view shows only that items which were reclassified to the new tariff code by error of data upload to the system.
The raw code and result of script is following:
Triggers - example of automation
In this section I was focus on adding more specific function to my database.
For example a script that allows you to upload new products into the table 'QUEUE' and add them automatically to the 'CLASSIFICATION' table. The table 'QUEUE' stored information new products to classification, person which is assigned to classify these products, customer id and date of delivered items by customer to processing.
After add the new items to the table 'QUEUE', also these items exist in the table Classification:
However, it is very important that if the person has already assigned a code in the 'CLASSIFICATION' table, then the already classified codes should disappear from the 'QUEUE' table.
So a new trigger was also used here:
When the new item will be added to the 'QUEUE' table, for example:
After set the trigger for process of update tariff code in the 'CLASSIFICATION' table, then the product id will be removed from 'QUEUE'.
Note #1: the database is created in Microsoft SQL Server Management Studio.
Note #2: all used data does not reflect reality. This data was created by me to not violate the confidential information policy.
Add comment
Comments