Ask Reuben

Category (Meta-Schema)

How can I group database tables?

How can I arrange Meta Schema Manager to show less tables ? 

How can I arrange Database Structure View to show less tables ? 

If I was to describe a typical developer, I might say they have a database schema with the number of database tables being in the hundreds, and that these database tables can be arranged into smaller groups.  They might have a word for these grouping that you recognise such as module, components, category, sub-systems, applications or even simply groups, I am used to the word module so will use that in this article

Their product might include a general ledger module, debtors module, creditors module, sales module, purchasing module, cash book module,  fixed assets module, works order module, inventory module, tax module, payroll module etc.  A customer might only purchase or enable a subset of these modules.  When a developer is given some work to do, a typical task would involve only working in one of these modules at a time.

When the developer is working in one of these modules, if they are looking in the .4db Meta-Schema Manager, or the Database Structure View, they don’t want to see the hundreds of database tables in the entire product, they would find it beneficial to only see the database tables in the module they are working on.

Databases typically don’t have a consistent mechanism to sub-divide all the tables into smaller palatable  groupings.  Any grouping may have been achieved via a naming convention.

What you have in Genero Studio is the concept of the Category property within the Meta-Schema Manager.  For each and every table, you can enter a category value.  You can then use the filter functionality to only view tables that have that particular category value.

… this is a free-text field, but values entered to date will appear in the drop-down.  Use the drop-down to ensure consistent data entry.

This value then appears in the Database Structure view in the Type column.  You can use the filter at the bottom to limit what tables then appear in the Database Structure view.  The screenshot on the left shows all tables, the screenshot on the right shows what happens when I enter the category value (cr) into the Filter field.  So in this case instead  of seeing all tables, I now just see the tables in the creditors (cr) module.

This next series of screenshots show the same concept in the Meta-Schema Manager.  The first screenshot shows 9 database tables.   I have then right-clicked, selected Filter Items and filtered on cr.  The last screenshot shows that the Meta-Schema Manager only shows the 3 tables that match that filter value.  If you look across the bottom, you can see I have created multiple views, one for each grouping of tables within the database as well as keeping one view that has all the tables.

By using the values entered into the category field in my filters, I have restricted the views to just the tables I am interested in for the task I might be working on.  This avoids the developer being saturated with too many tables, and they can better concentrate on the task they are trying to achieve.

In implementing you may need to consider a naming convention for category.  There is probably more functionality that could be added using this concept of category.  For instance should filter apply to table names only?  Should the Filter Views be automatically generated?  Can 4gl compilation give warnings if tables from wrong category are used?  Hopefully making you aware of this functionality can lead to other functionality improvements.