How do you document your database code to see dependencies between database objects?

Question

I want to write documentation on my pet project.

I have 30 tables and almost 50 views and about 30 functions (stored procedures) in my PostgreSQL database.

I would like to see where tables ( which views and which functions ) are used.

I would like to see where views ( which views and which functions ) are used.

I would like to see if a function is used by another function.

I would like also to write a description of each objects ( tables, views and functions )

and a short description of fields.

Shortly, I want to be able to see what use a specific object and which objects use a specific object.

I thought to use a text file and for every object. I might list objects that use the current one and which ones are used by the current one.

<pre>
Table aaaa
A short description
used by : view v_aaaa

id  int  primary key
name  varchar(30)   name of a...

================================
view v_aaaa
A short description

list of fields...

used by function f_aaaa
Depends on  table aaaa



==============================

function f_aaaa
A short description
Parameters ( description, IN/OUT )
Depends on view v_aaaa
           function fbbbb 

==============================

function f_bbbb
A short description
Parameters
Called by function f_aaaa

I'm afraid that my documentation will be unsynchronize quickly Imagine I add a function f_cccc that calls f_aaaa and f_bbbb. I'll have to modify doc on f_aaaa and f_bbbb

I know that UML describes relations about entities (It does that, right ???). But I want something simple and I don't want to follow a 75 hours training... And I'm not sure that you can have a "link" between entities and function as I want.

Do you have something to suggest me ?

I use PostgreSQL on Linux (Fedora). If you propose a tool that do this, it must be compatible with PostgreSQL :-)

For my code documentation, I use Doxygen.

Thank you

Answer 1

Just draw a EAR diagram. Entity-Relation diagram.

This tutorial should give you a good understanding of it. http://www.scribd.com/doc/7500847/entity-relationship-diagram

And you have this one http://www.getahead-direct.com/gwentrel.htm

Edit:

Lets say you have a tabe CAR, then you draw a box CAR:

CAR
----------
id (int)
name (vchar)
numbSeats (int)
----------
GetCar
SetCar
DeleteCar

Thelast 3 are your functions.

Answer 2

I don't document in order to see dependencies. Documentation is automatically out of date.

I use a tool for that. At present I use the products from ApexSQL, but I've used the Redgate tools in the past.

Answer 3

You can actually collect some of this information by querying the database's internal "depends" information. If something depends on another, that suggests it uses it. Here's a sample query to give you an idea how to traverse the two structures involved:

SELECT
  c1.oid as relid,
  n1.nspname || '.' || c1.relname as relation,
  c1.relkind,
  c2.oid as relid,
  n2.nspname || '.' || c2.relname as dependency,
  c2.relkind
FROM 
  pg_depend d,
  pg_class c1,
  pg_namespace n1,
  pg_class c2,
  pg_namespace n2
WHERE 
  d.objid = c1.oid AND
  c1.relnamespace = n1.oid AND 
  n1.nspname NOT IN('information_schema', 'pg_catalog') AND
  n1.nspname !~ '^pg_toast' AND
  d.refobjid = c2.oid AND
  c2.relnamespace = n2.oid AND 
  n2.nspname NOT IN('information_schema', 'pg_catalog') AND
  n2.nspname !~ '^pg_toast' AND
  c1.oid != c2.oid
GROUP BY n1.nspname,c1.relname,c1.oid,c1.relkind,
  n2.nspname,c2.relname,c2.oid,c2.relkind
ORDER BY n1.nspname,c1.relname;

Information about all these internal bits can be found in the system catalog documentation.

Answer 4

consider using a naming convention to reinforce SQL object dependencies:

Table1

Table2

vw_table1_byField1

vw_table1_byField1_table2

vw_table2

fn_table1

fn_table1_table2

Answer 5

I finally build a huge hmtl file.

File contains alot of anchors and it's easy to navigate to different objects.

It's a lot of work but it was exactly what I want :-)