How to have different tables for the first fields, but the other fields be common among them?

Hi all.

Yes, I suppose the title is not very clear. Let me explain my problem.

I want an app to map the processes in the company.

So I have type of process (core, support, management).

Then I have another table which is for macroprocesses. It belongs to the Type of Process table.

Then I have a table to processes… it belongs to Macroprocesses table (because a Macroprocess can have several processes, but each process belongs to a single macroprocess)

Then SubProcesses Level 1 and SubProcesses Level 2.

The problem is that when I reach the Process Level, all the other levels below have the same fields.

Process Owner, Main Department, Version, Date Modified, subtable for Processes Inputs and another for Outputs, Diagram Image, subtable for Documents related to that process, etc.

It seems counter productive to have three tables (Processes, SubProcess L1 and SubProcessL2, all repeating some 20 different fields.

Is there anyway to have like 3 different fields for those Tables, and then the other 17 fields show below, seamlessly?

(ps, I guess I could have a single Process Table and then choose the Process Level instead of a table for each level… but I created different tables for this ownership relation… a Process can have multiple subprocesses level 1… a subprocess level 1 can have multiple subprocesses level 2…)

1 Like

I believe you may wish to mention the exact reason such as below you are seeking a solution for so that community could suggest a possible solution

  1. One time creation of a large number of tables with an identical column structure

  2. User experience in probably having to navigate to many similar views with different names

What are the differences between SubProcess Level 1 and Level 2? If it’s only the level itself to be different, then I’d use the same table for both, and the Level is a column of the table (you can use Enum type for restricting the choices).
Regarding MacroProcesses, what are they? Are they simply a series of processes? In this case, you need one table for MacroProcesses and one for Processes. A column in Processes references MacroProcesses table. Are the 20 columns necessary for MacroProcess or should you inherit the columns of the Processes? If you can inherit them, you don’t need to define the columns in the MacroProcesses table.

As I mentioned, the problem arises at the Process Level, so no need to worry about macroprocesses. Macroprocesses work only as a logical agglomeration of processes. It is at Process Level that there are tons of parameters for each process, which would generate too many columns and thus make no sense to have two (or more) tables with all those repeated columns.

There are no differences between Process, SubProcess Level 1 and SubProcess Level 2.

The ONLY difference is that a Process can have many subprocess level 1… and a subprocess level 1 can have many subprocess level 2.

That is… there is a 1 - n relationship between the different levels.

Process, SubProcess Level 1 and SubProcess Level 2:

GENERAL INFORMATION TAB

  • Name of Process (short text)
  • Description of Process (long text)
  • Is it External Process? (checkbox)
  • Aim of the Process (long text)
  • Trigger (long text)
  • Result (long text)
  • Scope (related table of organizational units)
  • Scope Description (long text)

ORGANIZATION tab
-Process Owner (related table of employees)
-Process Manager (related table of employees)

  • Methodical Reviewer (related table of employees)
  • Process Analyst/designer (related table of employees)

INPUT/OUTPUT tab

  • Input (related table of inputs)
  • Input: quality requirements (long text)
  • Output (related table of outputs)
  • Output: quality requirements (long text)

SYSTEMS/PRODUCTS tab
-Referenced Applications / IT Systems (related table of systems and applications)

  • Referenced Products (related table of products)
  • Technical Resources (related table of technical resources)

DOCUMENTS tab
-Referenced Documents (related table to documents)
-Terms Abbreviations (link to external document)

  • Terms Abbreviations (related table to terms and descriptions)

CUSTOMERS/SUPPLIERS tab

  • Process Customers (related table to customers (organization units, roles, external)
  • Process Suppliers (related table to Suppliers (organization units, roles, external)

CONTINUOUS IMPROVEMENT tab

  • Critical Success Factors (long text)
  • Performance Indicators (related table of KPIs)
  • Process Indicators (related table of Process Indicators)
    -Opportunities for Improvement (related table of OFIs)

RISK MANAGEMENT tab

  • Assigned Risks (related table of risks)

An example:

Type of Process : Management Process
Macroprocess: Quality Management
Process: Audit Management
SubProcess Level 1: Internal Audit Programming.