Database Schemas

Owned by ivan.neganov (Unlicensed)
Last updated: Oct 26, 2021
2 min read

Vessel Registry information will be spread across the following 3 databases:

  1. ClientDetailDatabase - contains information about clients involved in vessel registration. We expect the schema of this database to change in the future, once Stakeholder Management Service takes over client management concerns.

  2. VesselDetailDatabase - contains detailed information about vessels.

  3. VesselRegistryDatabase - contains information about registration of vessels. In essence, it "links" vessel detail records with client detail records.

Updated schemas of each of these three databases are listed below along with relevant notes.

ClientDetailDatabase

We have copied the schema of CONTACT_INFORMATION table from the existing WorkManagementDatabase into the ClientDetailDatabase, renaming it into Y100_CLIENT, and made the following changes:

  1. Removed fields PRIMARY_CONTACT_IND, CONTACT_UNIQUE_ID and PRIMARY_CONTACT_ID, as they are not relevant to the scenarios surrounding the Vessel Registry management;

  2. Added fields: FIRST_NAME, LAST_NAME, MIDDLE_NAME, as we feel they will simplify migration story from the legacy SCVLS database, where equivalent fields are present.

  3. Added fields: BUSINESS_NUMBER, CLIENT_TYPE, COMPANY_NAME as this table will be used to store both individuals and businesses as “clients”.

  4. Added miscellaneous fields: PREFERRED_LANGUAGE, OWNER_STATUS, OWNER_QUALIFIED_STATUS, FAX_NUMBER.

VesselDetailDatabase

The only change made here was adding OFFICIAL_NUMBER field to the Y001_VESSEL table, since official number is an attribute of a vessel, and never changes once assigned to one.

 

VesselRegistryDatabase

The following changes were made to the database:

  1. Dropped table Y015_REGISTRY_WORKITEM_XREF. Reason: Vessel registration records should not “have knowledge” of work items or requests that have lead to their creation or modification. This decouples the request management process from vessel registry and allows for applying security, data loss prevention and other polices to these two databases independently.

  2. Added new table Y010_VESSEL_OWNERSHIP, in accordance with the diagrams developed by @vincentjoseph.chazhoor (Unlicensed). A couple of deviations here:

    1. Instead of using FK relationship to Y001_VESSEL, which is in another database, we use FK relationship to the Y010_REGISTRY table.

    2. CLIENT_ID column is no longer marked as a FK, as it points to records in a separate database.

  3. Added OFFICIAL_NUMBER field to the Y010_REGISTRY table, as this is how vessel records will be linked to the vessel registry records.