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
from
(
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
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE' AND LEFT(ROUTINE_NAME, 2) != 'dt'
) as X
Print | posted on Monday, February 26, 2007 2:42 PM

Feedback

# re: The Abstraction Indicators - A Database Design Measure

left by Tara at 2/27/2007 7:00 AM Gravatar
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

left by Jon at 2/27/2007 7:59 AM Gravatar
Nice post:

PA VA
1.58870967741935483 .19354838709677419

# re: The Abstraction Indicators - A Database Design Measure

left by Adam Machanic at 2/27/2007 10:23 AM Gravatar
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.

# re: The Abstraction Indicators - A Database Design Measure

left by Joe Celko at 3/5/2007 1:06 AM Gravatar
Have you ever looked at Function Point measurements? I am not sure how well they will work for a declarative language.
Comments have been closed on this topic.