Uploaded image for project: 'mod-orders-storage'
  1. mod-orders-storage
  2. MODORDSTOR-78

DB schema enhancements

    XMLWordPrintable

    Details

    • Type: Story
    • Status: Closed (View Workflow)
    • Priority: P3
    • Resolution: Done
    • Affects Version/s: None
    • Fix Version/s: 6.0.0
    • Labels:
      None
    • Template:
    • Sprint:
      ACQ Sprint 64
    • Story Points:
      3
    • Development Team:
      EBSCO - FSE

      Description

      Purpose

      1. An order line can be created only if corresponding order exists so the foreign key (FK) should be created. Also a piece record can be created only if order line exists.

      Approach

      Define foreign keys

        "tables": [
          ...
          {
            "tableName": "purchase_order",
            "fromModuleVersion": 1.0,
            "withMetadata": true,
            "populateJsonWithId": true,
            "customSnippetPath": "purchase_order_table.sql",
            "ginIndex": [
              {
                "fieldName": "poNumber",
                "caseSensitive": false,
                "removeAccents": true
              }
            ],
            "uniqueIndex": [
              {
                "fieldName": "poNumber"
              }
            ]
          },
          {
            "tableName": "po_line",
            "fromModuleVersion": 1.0,
            "withMetadata": true,
            "populateJsonWithId": true,
            "foreignKeys": [
              {
                "fieldName": "purchaseOrderId",
                "targetTable": "purchase_order"
              }
            ],
            "ginIndex": [
              {
                "fieldName": "title",
                "caseSensitive": false,
                "removeAccents": true
              }
            ],
            "uniqueIndex": [
              {
                "fieldName": "poLineNumber"
              }
            ]
          },
          {
            "tableName": "pieces",
            "fromModuleVersion": 1.0,
            "withMetadata": true,
            "populateJsonWithId": true,
            "foreignKeys": [
              {
                "fieldName": "poLineId",
                "targetTable": "po_line"
              }
            ]
          }
        ]
      

      Note: pieces table definition should be moved after po_line due to FK

      Scope:

      Introduction of the foreign keys will also prevent records deletion if they are referenced by other entities i.e.

      • PO cannot be deleted if there is at least one PO Line
      • PO Line cannot be deleted if there is at least one piece record
      References

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                piotr_kalashuk Piotr Kalashuk
                Reporter:
                piotr_kalashuk Piotr Kalashuk
                Tester Assignee:
                Craig McNally Craig McNally
                Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases