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.
Oracle Customer Model and Multi-Org
Here I used shading to distinguish between org-striped, org-linked (my term) and other entities.
Structured Design Methodology
The methodology involves a sequence of diagrams and tables, so I have not extracted a diagram in this case.
SQL Special Structures
Multiple Table Instances with Scalar Subqueries in Where Clause
Subquery Factor
Selecting Database Function
Selecting Scalar Subqueries
Nested Analytics Subqueries
Model Clause
Recursive Subquery Factor
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.
Excel Array Object
This object was implemented in Perl.
