We are developing a prototype for a product, which is going to have loads of stored procedures in it (with nestings within them). We are using visio to document our table design & all related stuffs.. But couldnt really figure out a concrete approach for documenting our sprocs.. Though all sprocs will be heavily commented, we want to have an overview of what procs are available, which one calls what & those sort of info.. Have any of you guys produced those kinds of docs ? I just dont want to reinvent the wheel....
It would be helpful to classify your stored procedures, for example:
- Table APIs
- - Employee Table APIs
- - - create_emp
- - - delete_emp
- - Department APIs
- Business Processes
- - HR processes
- - Payroll processes
- - - run_payroll
- - Printing
- - Error handling
(These are just off the top of my head).
If your DBMS supports packages (like Oracle), you should use them to keep related procedures together.
Then for each procedure you can write documentation to whatever level seems appropriate. It would be a good idea to look at documentation for your DBMS's own stored procedures or packages as a guide.
payroll_number IN payroll_number_type: mandatory
week_number IN week_number: mandatory
(Describe the purpose of the procedure)
The level of detail here depends on the intended audience. If you want to show what stored procs call this one, and are called by this one, you could do that here. In my experience, this sort of information can be useful as a guide, but becomes unreliable over time - i.e. is indicative not definitive.
Personally, I would not be interested in representing any of this as a diagram, except maybe to show interrelationships in a particularly complex area with dependencies. It would work very nicely as a set of HTML docs with hyperlinks to navigate the hierarchy, and to jump to related information.
This may not be appropriate, especially if you use a lot of graphical tools, but one approach is to use standard techniques for documenting code.
This means you'd have a requirements document, a design document, user documentation, and all the SQL code necessary for building the database from scratch stored in a CVS tree.
It's a good habit to get into, IMHO.
Oh, BTW, there are better tools than gnumake. Ant is a good one. Granted, it's Java-centric, but it works and is not prone to evil whitespace glitches like make.
I was reading over Tony's post... Is there anything for SQL for reading internal comments, organizing them automagically and producing documentation? Something like Javadoc? Sure, you can do it by hand, but it would make sense to put it straight in the code.