DavidM

Over the years, several of my contracts involved analysing and critiquing third party databases.

On arrival at the client site, I would be given the business model specification, a copy of the database and a room/cubicle to work in.Obviously depending on the complexity of the model, it could take me weeks to give a full analysis.

Mostly the report would be centred around normalisation, data integrity, scalability and the quality of the implementation of the business model within the database.

On final delivery of report, the client (usually the CIO or CEO) would take me out for a coffee and discuss the results. In most cases, they would ask me to cut down the technobabble and give it to them straight. Essentially they wanted me to provide some sort of score for the database.

In response, I devised a series of metrics that would allow a layman to interpret my results quickly.

I present to you the metric that had the most impact on my clients..... The Hermetic Ratio.

Named in honor of the legendary Hermes Trismegistus the Thrice Great, the ratio was designed to capture the "wholeness" of the database.

It is a measure of the databases integrity features and is centred around the concept of domain/type implementation. Put simply it is the numberof constraints defined by the number of data elements being captured.

The ratio has 2 contexts

1. Individual tables
   • Each column in a table should have a domain/type defined. The base types supplied by most DBMS are inadequate for every day use and should be constrained further by CHECK constraints.
   • Each table has its own Hermetic Ratio
2. The Database
   • The Hermetic Ratio of the database is the average Hermetic Ratio of all tables

Interpreting the results

• For each table
  • Excellent >= 1.00
  • Acceptable >= 0.5
  • Poor < 0.5
• Database
  • Excellent >= 0.75
  • Acceptable >= 0.5
  • Poor < 0.5

I have never seen any moderately large database schema with a Hermetic Ratio over 0.75 This is primarily caused by the addition of logging and audit tables which understandably score very low.

The most frightening results always appear from those schemas generated from Object/Relational tools and the big ERP and CRM implementations.

I DARE YOU ALL TO RUN THIS OVER YOUR CURRENT DATABASE.

Anybody who scores over 0.5 for the database ratio should pat themselves on the back......

Without further ado...

-- Individual Table Hermetic Ratio
Select TABLE_NAME, cast(CHKCount as money)/ColumnCount as HermeticRatio
from
(
Select T.TABLE_NAME
    , COUNT(CC.COLUMN_NAME) as ColumnCount
    , (SELECT COUNT(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS CK where CK.TABLE_NAME = T.TABLE_NAME) as CHKCount
from INFORMATION_SCHEMA.TABLES T
inner join INFORMATION_SCHEMA.COLUMNS CC on CC.TABLE_NAME = T.TABLE_NAME
where TABLE_TYPE = 'BASE TABLE'
group by  T.TABLE_NAME
) as X

-- Database Hermetic Ratio
Select AVG(HermeticRatio) as HermeticRatio
from
(
Select TABLE_NAME, cast(CHKCount as money)/ColumnCount as HermeticRatio
from
(
Select T.TABLE_NAME,  COUNT(CC.COLUMN_NAME) as ColumnCount
    , (SELECT COUNT(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS CK where CK.TABLE_NAME = T.TABLE_NAME) as CHKCount
from INFORMATION_SCHEMA.TABLES T
inner join INFORMATION_SCHEMA.COLUMNS CC on CC.TABLE_NAME = T.TABLE_NAME
where TABLE_TYPE = 'BASE TABLE'
group by  T.TABLE_NAME
) as X
) as Y