DbView Database visualization

Soft foreign key detector

Soft foreign key detectors are used with soft foreign keys.

Some database engine does not support foreign key constraints. Or a database may be created by inexperienced developers, which don't know about foreign keys constraints... and someone asks you to work on the database.

In this case, you typically adopt a naming convention that allows the reader to detect foreign keys.

For example : "fk_product_price" (declared in the table "command") is a foreign key. It is associated to a join from the "dependent" table "command" to the "reference" field "price" (within the "reference" table "product)".

DbView provides a set of detectors. Each detector is associated with a naming convention. You can get the list of all available detectors by typing the following command:

dbview.bat list-soft-key-detectors

dbview.sh list-soft-key-detectors

You get something like this:

        target-table-name-us-id
        Pattern:      {name of the target table}_id.
        Target field: Always "id".
        Example:      product_category_id
                      -> product_category.id

        id-us-target-table-name
        Pattern:      id_{name of the target table}.
        Target field: Always "id".
        Example:      id_product_category
                      -> product_category.id

        fk-target-table-name
        Pattern:      fk{name of the target table}.
        Target field: Always "id".
        Example:      fkproduct_category
                      -> product_category.id

        fk-us-target-table-name
        Pattern:      fk_{name of the target table}.
        Target field: Always "id".
        Example:      fk_product_category
                      -> product_category.id

        id-target-table-name
        Pattern:      id{name of the target table}.
        Target field: Always "id".
        Example:      idproduct_category
                      -> product_category.id

Name	Description
target-table-name-us-id	This dectector assumes that you use the following naming convention for foreign keys : `NameOfTheReferenceTable_id` For example, if DbView comes across the field "`product_id`", then it assumes that it is a foreign key : "`product_id`" is a foreign key to the "reference" field "`product.id`".
id-us-target-table-name	This dectector assumes that you use the following naming convention for foreign keys : `id_NameOfTheReferenceTable` For example, if DbView comes across the field "`id_product`", then it assumes that it is a foreign key : "`id_product`" is a foreign key to the "reference" field "`product.id`".
fk-target-table-name	This dectector assumes that you use the following naming convention for foreign keys : `fkNameOfTheReferenceTable` And it assumes that the name of the "reference" field is always "id". For example, if DbView comes across the field "`fkproduct`", then it assumes that it is a foreign key : "`fkproduct`" is a foreign key to the "reference" field "`product.id`".
fk-us-target-table-name	This dectector assumes that you use the following naming convention for foreign keys : `fk_NameOfTheReferenceTable` And it assumes that the name of the "reference" field is always "id". For example, if DbView comes across the field "`fk_product`", then it assumes that it is a foreign key : "`fk_product`" is a foreign key to the "reference" field "`product.id`".
id-target-table-name	This dectector assumes that you use the following naming convention for foreign keys : `idNameOfTheReferenceTable` And it assumes that the name of the "reference" field is always "id". For example, if DbView comes across the field "`idproduct`", then it assumes that it is a foreign key : "`idproduct`" is a foreign key to the "reference" field "`product.id`".

For information, all the soft key detectors are packed into a JAR file :

extensions/softForeignKeyDetectors.jar

You can print the content of this file by using the Java archiver (aka "jar").

jar tvf extensions\softForeignKeyDetectors.jar

The above list of foreign key detectors does not cover all the possible naming conventions.

Adding a new detector is made easy if you have some basic knowledge in JAVA. Adding a new detector is a three-step procedure:

Write a class (part of the package org.dbview.resources.softforeignkeydetectors).
Compile the class.
Add the compiled class to the JAR that contains all the soft key detectors (jar uf softForeignKeyDetectors.jar youNewDetector.class).