How to implement a catalogue for meta-data and automating SQL in a database?

Question

I have read here the discussions on 5NF, EAV and 6NF and the need for a catalogue to handle meta-data and the complex SQL "automatically". How is that implemented in practice?

PerformanceDBA wrote several answers on 6NF and EAV that mentions catalogues, e.g. in the following questions:

• Would like to Understand 6NF with an Example

• 6NF and historical attribute data

• and especially Multiple fixed tables vs flexible abstract tables, where PerformanceDBA wrote

  "Eg. For 6NF databases with a catalogue, I have a set of procs that will [re]generate the SQL required to perform all SELECTs, and I provide Views in 5NF for all users, so they do not need to know or understand the underlying 6NF structure. They are driven off the catalogue. Thus changes are easy and automated. EAV types do that manually, due to the absence of the catalogue."

Answer 1

First, with LedgerSMB we reuse the system catalogs and information schema wherever we can. This means that the application actually spends some time querying the system catalogs. We also have some meta-data calculations for extended attributes. We don't do EAV here. Rather we have actual relations and meta-data about these which allows us to create relational queries on the client side. These are loaded at one point and cached. The catalog looks very much like an EAV catalog, but the underlying storage is actually relational and the functions which maintain these alter underlying tables. This gives you the flexibility of EAV without the underlying difficulties.

In future versions we will probably move to fewer application catalogs and greater use of the Pg system catalogs and information schema, and our interface will be simpler from an application perspective.