Validate your classification

Complex data models with hunderds of tables can be challenging to classify correctly! Luckily DATPROF Subset comes with some handy tools to improve your classification. Often the Suggest wizard will come up with a good first classification, but there are some other features we can use as well.

Move all the table to the Subset classification, except the start table
Select all tables and right click Count rows

In the Gasware Training database there are no empty tables., but typically you should move all the tables that contains no rows to the empty classification. Often a small amount of rows is a good identifier for a type/category table that should be classified as Full.

Under each classification is a small validate button. Clicking this button will validate the classification in relation to other tables

Subset validation: If a subset classified table has no direct or indirect relation with the start or another subset table, this will result in a warning. In that case, there is no criteria on how this table can be filtered. Classify the table as full or empty, or manually add the missing foreign key.

Full validation: If a full classified table references tables that are classified as start, subset or empty, this will result in a warning. DATPROF Subset will copy all the data, but this will probably include data that reference data that is filtered out by the start, subset or empty classification. In database terms this is a referential integrity issue. The child records is referring to missing parent records. This will cause foreign key violations.

Empty validation: If an empty classified table is referenced by tables that are classified as start, subset or full, this will result in a warning. Subset will not copy any data, but others table probably include data that reference data in the original empty table. This will also lead to referential integrity issues. The parents records are missing, but the child records are copied fully or subsetted.

Examples

For learning purposes, let's try to manipulate the classifications in such a way, that we get classification warnings.

Subset classification warning

Make sure that all tables are classified as Subset and the CUSTOMERS is selected as Start Table
Right click the CONTRACT_DETAILS table and click Properties...
Go to the Foreign Keys tab
Select the FK_CONTRACT_DETAILS_CONTRACTS foreign key and click Delete and Yes to confirm
Click Close
Now click the Validate button
under the subset classification
Notice that icon for the CONTRACT_DETAILS table has changed into a warning icon
In the upper menu bar, click Visualize → Process model...
Click Visualize in the popup confirmation.
Notice that CONTRACT_DETAILS is directly under or above the CUSTOMERS start table and has no dependencies with any other table.

To fix this, we will add the 'missing' foreign key

Close the visualization window
Right click the CONTRACT_DETAILS table and click Properties...
Go to the Foreign Keys tab
Click the Add foreign key button
Enter as Foreign key name: FK_CONTRACT_DETAILS_CONTRACTS
Choose the parent table schema
Choose CONTRACTS as Parent table
Double click the empty cell under This table and choose CONTRACT_ID
Double click the empty cell under Parent table and choose CONTRACT_ID
Click OK to add the foreign key and close the constraint editor
Click Close to close the properties window
Now validate
the subset classification again. The warning icon will be removed
Visualize the process model to confirm that the CONTRACT_DETAILS has a dependency with the CONTRACTS table

Full classification warning

Move the CONTRACT_DETAILS to the full classification
Click the Validate button
under the full classification and notice how the Warning icon appear for the CONTRACT_DETAILS table

CONTRACT_DETAILS refers to CONTRACTS which is being subsetted (filtered). This means that with the current classification we will probably copy data into the CONTRACT_DETAILS table that refers to the CONTRACT data that is filtered out. This can lead to foreign key violations on the CONTRACT_DETAILS records.

There are multiple ways of fixing this issue

Let's move the CONTRACT table to the full classification and validate
the full classification again.
Notice that CONTRACT_DETAILS is not in warning anymore. The CONTRACTS table however is now in warning.
Right click the CONTRACTS table and go to Properties...
Go to the Foreign Keys tab and investigate the different foreign keys on the CONTRACTS table

There are four foreign keys that references the CUSTOMERS, LOCATIONS, PRODUCTS and SERIALS tables. Those tables are currently classified as start and subset. The best option is to move CONTRACTS and CONTRACT_DETAILS to the subset classification

Move CONTRACTS and CONTRACT_DETAILS tables back to the subset classification

Empty classification warning

Move the CONTRACTS table to the empty classification
Click the Validate button
under the empty classification and notice how the Warning icon appear for the CONTRACTS table

The TASKS and CONTRACT_DETAILS tables reference to data in the CONTRACTS table. This means that probably data in the TASKS table misses the parent records in the CONTRACTS table, because it's classified as empty. The CONTRACT_DETAILS in the subset classification also gives a warning when validating, because it has no link to the start or subset tables.

To fix this, move the CONTRACTS table back to the subset classification

If you got stuck in validating your classification, just watch the following video.