byrmol Blog


The Abstraction Indicators - A Database Design Measure

Before I kick this one off, please remember that these measures can only be taken into consideration when the full scope of the database model and its implementation are known. 

The Abstraction Indicators try to measure the databases capacity to cope with change and the "profile" of the database to calling applications.  Abstraction in this context means to hide internal schema changes from users.

These indicators are relevant to the entire database only and must be taken with a huge grain of salt.

There are 2 components

  1. View Abstraction
    • I won't go into the all the reasons why views are so good, but simple state that the power of a view to abstract the underlying model is second to none.
    • This indicator considers the most basic relationship possible between 2 tables that a view could practically be considered for.  (PK-FK)
  2. Procedure Abstraction
    • Stored procedures (when used with a security model) effectively create an API for calling applications/users.
    • Based on the principle of CRUD, this indicator assumes that 4 procedures are needed to abstract a table from a user.

Interpreting the results

  • View Abstraction
    • A score over 1.00 usually indicates a well thought out implementation. 
    • Scores below 0.10 need  to be investigated.
  • Procedure Abstraction
    • A score over 1.00 is assumed to have a tight API defined.  This assumes that each table has a CRUD equivalent.
    • Any score under 0.25 usually means that the calling application is doing most of the SQL writing.

Select ProcCount/4.00/TableCount as ProcedureAbstraction
    , ViewCount/0.5/TableCount as ViewAbstraction
Select count(*) as ProcCount
    , (Select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE') as TableCount
    , (Select count(*) from INFORMATION_SCHEMA.VIEWS ) as ViewCount
) as X

Legacy Comments

re: The Abstraction Indicators - A Database Design Measure
For the database where I have a lot of involvement in (as compared to those developer designed databases):

ProcedureAbstraction ViewAbstraction
------------------------------- ------------------------------
1.79807692307692307 .30769230769230769

re: The Abstraction Indicators - A Database Design Measure
Nice post:

1.58870967741935483 .19354838709677419

Adam Machanic
re: The Abstraction Indicators - A Database Design Measure
Interesting post, I think you need to think beyond this. What if either score is, say, 10.0? Such a database--with a complex network of slightly different stored procs--is a NIGHTMARE to maintain! Last year I worked on one with around 200 tables and several thousand (3500, IIRC) stored procedures, plus something like 1000 views. A too-high score is not good.

Joe Celko
re: The Abstraction Indicators - A Database Design Measure
Have you ever looked at Function Point measurements? I am not sure how well they will work for a declarative language.