Like many SQL developers I have always used entity-relationship diagrams to help in writing queries, and would extract sections to document them. Some years ago, however, I realised that having a single static diagram was not sufficient for complex queries with large numbers of tables, structures such as inline views, and multiple table instances. I therefore developed a diagram-based design methodology that I published in May 2009 on scribd. Since then I have extended the ideas in that approach to develop diagrams to cover various additional structures in SQL and in other areas. These diagrams were developed as needed for particular scenarios and have been published in several documents on scribd. I thought it would be a good idea to bring them together in one place, namely here, with example diagrams and the scribd document embedded thereafter. [Incidentally, I wonder what readers make of this 8-dimensional document structure?]

I would categorise them under four headings:

  • Entity-Relationship Diagrams
  • Structured Design Methodology
  • SQL Special Structures
  • Object Structures

Entity-Relationship Diagrams

Oracle Spatial Schema

The embedded document below also includes an ERD of the much simpler HR schema, but this one is more interesting as it shows extensive use of subtypes. The document is concerned with networks and I superimposed tree and non-tree network links on the diagram.

HR and Network Diagrams - SDO ERD

An Oracle Network Traversal PL SQL Program by Brendan Furey

Oracle Customer Model and Multi-Org

Here I used shading to distinguish between org-striped, org-linked (my term) and other entities. Xilinx ERDs TCA Key

Oracle Multi-Org - AR and OM Data Structure Changes by Brendan Furey

Structured Design Methodology

The methodology involves a sequence of diagrams and tables, so I have not extracted a diagram in this case.

A Structured Approach to SQL Query Design by Brendan Furey

SQL Special Structures

Multiple Table Instances with Scalar Subqueries in Where Clause

JNSQ Join Subquery

Subquery Factor

WJSQ With Subquery

Selecting Database Function

FNSC Database Function

Selecting Scalar Subqueries

WJKP Select Scalar Subquery Keep

SQL Pivot and Prune Queries - Keeping an Eye on Performance by Brendan Furey

Nested Analytics Subqueries

Ranges Analytic Key

Model Clause

MOD SPT

Recursive Subquery Factor

RSQ NOV

Forming Range-Based Break Groups With Advanced SQL by Brendan Furey

Object Structures

I use a different type of diagram for object structures from those for SQL and ERDs, and it’s intended to be very general, being independent of programming language and applicable to any object structure, allowing arbitrary nesting of array and record types.

Code Timer Object

This object was implemented in three languages: Oracle, Perl and Java.

Code Timer object

Code Timing and Object Orientation and Zombies by Brendan Furey

Excel Array Object

This object was implemented in Perl.

Perl XL

A Perl Object for Flattened Master-Detail Data in Excel by Brendan Furey