The Old Joel on Software Forum: Part 3 (of 5) - Stored Procedure Organization

Stored Procedure Organization

I have taken over responsibility for a new database, which has over a thousand stored procedures (SP’s). What occurred to me is that for a given problem that may need an SP to function, how would a new developer know anything about the existing SP’s? In other words, the only thing going for an SP is the naming convention left to the user. There is no equivalent to Java Doc for the Transact-SQL world. Though the inputs are relatively well defined, the outputs most certainly are not. How do you deal with this? Do you know of any tools that organize the DB objects visually and support some form of SP documentation or coherent modeling for that matter?

m
Friday, January 9, 2004

You're correct that a sql server by itself doesn't do too much for you in regards to documentation. The best you can do is include consistent header comments and just comment the code well in general. Regardless of what you do, you've most likely got a good deal of documenting to do .

There are many database development tools / ERM apps you can use which store a lot of extra information about your database and can even generate very hefty documentation for you. Microsoft Visio (only in certain versions though), Erwin and others can be expensive, but there are some free tools around which do a nice job, too. Maybe we'll get some other people to post their favorite tools. I've been using Visio for the past few projects I've done mostly because I have an MSDN subscription and it comes with that.

One note I can make is that it's up to you to make sure your design/documentation is kept in sync with your actual database since it's easy to get them out of sync.

Wade Winningham
Friday, January 9, 2004

We use a consistent comment header... Most people seem to use it.

Rick Watson
Friday, January 9, 2004

If you can impose a pro forma comment header, then I wouldn't think it would be too hard to write a small app that scans the SP's and builds an index (like ndoc does for .net xml comments)

Then just schedule a job to run the app once a week and put a link to the comments page on your portal/wiki/homepage.

Philo

Philo
Friday, January 9, 2004

Thanks for the suggestion. Perhaps adding to the structured comments might be an index of dependant tables. For example if you want your program to update a customer phone number, you could search for the Customer table and get back a list of SP's and browse their inputs and comments.

Another request to MS (Philo, are you listening?) is to have some grouping of SP's. For example modules or packages depending on your flavor. You could then execute Customers.UpdateRecord val1, val2...

Perhaps the .NET language integration with Yukon might get us closer to this, though hopefully not loose the optimization abilities of t-SQL.

Overall, I would say managing the Stored Procs seems to be the messy part of DB applications with nice models and tools for DB schemas and programming languages that access the SP's.

m
Friday, January 9, 2004

Great idea with the grouping.

Wade Winningham
Friday, January 9, 2004

I like the grouping idea. I guess it's kind of an obvious answer, but it helps to have a naming convention for the SP's, like tablename_action_somedescription. Actually, no matter what the heck else you do, the naming convention is pretty crucial.

So you have...
spCustomer_Get_Invoice
spCustomer_Get_Shopping_Cart
spCustomer_Delete_Bank_Account_Because_I_Felt_Like_It
spCustomer_Delete_Cart_Item
spCustomer_Delete_Cart_All

Not necessarily the total answer for a system with thousands of SP's, but it's a start.

John Rose
Friday, January 9, 2004

Perhaps this might be of some help?

http://www.eggheadcafe.com/articles/20030609.asp

Code Monkey
Friday, January 9, 2004

tablename_action_somedescription would sort it by table, not by action; more like

spCustomer_Cart_Delete_Item
spCustomer_Cart_Delete_All
spCustomer_Cart_Get_Item
spCustomer_Cart_Insert_Item
spCustomer_Cart_Update_Item
spCustomer_Invoice_Get
...

Christopher Wells
Friday, January 9, 2004

Yukon has schemas.

Will that do the trick?

Philo

Philo
Friday, January 9, 2004

We treat stored procedures as "methods" on table "classes." Not _exactly_ UML-compliant, but it allows us to keep things organized. (And plus, the UML was designed to be extensible, right!)

What we do is:

1. Document the stored procedures in a UML class diagram. The stored procedures are typically allocated during detailed design when we create sequence diagrams.
2. Group the stored procedures by the main table it operates against. (When we have a hierarchy of parent/child tables, we put the stored procedure on the "top-level" parent table.)

We use a low-cost CASE (but high-value) tool called Enterprise Architect (not to be confused with Visual Studio Enterprise Architect.)

See http://www.sparxsystems.com.au for a free trial copy.

One nice benefit of this tool is its extension of the UML to handle database-centric modeling. At US$149 a license, it was the right price, too.

As far as naming goes, we use the aforementioned method of naming the stored procedure with an "object-action" style. For example:

Customer_Select (Gets a list)
Customer_Get (Gets a single row)
Customer_Insert
Customer_Delete

Of course, if you're working with an existing database, this may not help much since I don't think you'll want to go renaming everything.

Just my $0.02.

Dave

Dave
Monday, January 12, 2004