Printable View
This thread is related to the second article in the SqlCredit series: Part 2: Creating the Database, Tables, CRUD Procedures.
What do you like or dislike about the stored procedures in the attached code?
Remember that these procedures have to support both SQL Server 2000 and 2005.
Rob
I had an internal e-mail conversation with Greg Grater, one of the database experts here at Corillian. It is copied below with just a few changes to hide details of customers and projects. It reads top-to-bottom.
Greg
I have some opinions around the CRUD stored procedures and the naming of those stored procs.
In my databases, I like to follow this standard…
<prefix>_<Object>_<action>
Where action might be something like
- ReadByCardnumber
- ReadByAccountID
- ReadByAccountNumber
- DeleteByAccountID
- PurgeByDate
- PurgeByStatus
- Etc…
What do you normally do and why?
Rob
We used to always use the prefix (“CCSP_”, “CCSPAM_”, etc.). We dropped that because people didn’t think it was needed. I’m fine with leaving it off.
If we consolidated databases (and used 2005), we would likely use different schema to separate objects.
I have used <action> before <object> just because that was how I started with a standard. I actually like the idea of using the <object> first because of sorting. The only issue I see here is reads. If you have a read proc that reads from obj1, obj2, and obj3, what do you call it?
Greg
In “most” cases a read from multiple objects has a primary purpose and thus a parent object that makes it clear. For instance, I have several tables that are involved in the XX_CardTransactions_ReadByTransactionID, but the net result of the read is to list card transactions and their children’s data.
The sorting of objects is paramount in my opinion. Although SQL 2005 schemas provide some sorting and grouping, our products are vast and complex. By using the object name first, we easily avoid some duplication and make it easier to maintain architecture changes down the road. For instance: We had a table called ApplicationNotes that contained notes for a user’s application. Later down the road, a new requirement to capture the same kind of notes for App2 bubbled up. The knee jerk reaction was to create a second table called App2Notes. But, because the stored procedures were sorted/grouped in the manner described below, we were able to quickly assess that we could change the table name to Notes add a type column and quickly restructure the associated stored procedures into the following:
- XX_Notes_Create
- XX_Notes_CreateApp2Note
- XX_Notes_CreateApplicationNote
- XX_Notes_ReadApp2NotesByThisID
- XX_Notes_ReadApp2NotesByThatID
- XX_Notes_ReadApplicationNotesByThisID
- XX_Notes_ReadApplicationNotesByThatID
- XX_Notes_ReadByThisID
- XX_Notes_ReadByThatID
Having the object name as the primary sort criteria has been extremely valuable for maintaining the structure, but also for the developers to understand the structure and what is available. They can quickly review the stored procedures and reverse engineer most of the functionality available to the object simply because they only have one place to look in the list of stored procs.
Rob
Great ideas.
Personally, I love the ObjectVerbModifiers naming convention. The sorting obviously makes it much easier to find stuff and to prevent creating redudant sprocs. It also seems to lead to more predictable names so that people don't even have to look up the list. I suspect that's because the VerbObject style is more like English, which is more loose and unpredictable.
I've used this convention on a few recent projects and found it to be a joy, both from a DB engineer perspective and that of a middle tier developer.
However, the main downside is that web services more often use the VerbObject convention. Can you have your web service interface use one convention and your sprocs another? Of course. But if you're using a data access layer (DAL) that provides more automated support when your operations and sprocs are using the same convention, you'd have to decide if the convention is worth it. Better yet, you could just modify your DAL to automate things either way.
I highly recommend a object naming convention of [Object][Action][Modifier]. I go one step further and recommend staying with the CRUD Actions -instead of SELECT, GET, FIND, etc., use READ. (Some sprocs may have non-CRUD actions such as CALCULATE.) A large DEV team on a large project can be very creative in naming. I want to be able to minimize duplication of effort, and ease of locating (and reusing) similar existing code should be the primary focus of how names are created.
Also, what value is added by a [PREFIX]. A lot of folks use sp, usp, etc. (hopefully, not many use [sp_]), and what is the added value? You know that it's a sproc by context. You are needlessly wasting keystrokes. However, sometimes it is necessary to create a sproc in dbA that runs against data in dbB, and in those situations, I recommend using the db name (or code) as a prefix.
After a quick glance at the scripts, this is what I found.
CreateDatabase.sql - Everything is harcoded. Should be more flexible. My Company name could be SQLCredit but my database could be CreditTracker.
StoredProcs.sql - Should use Try Catch, COmmit and rollback based on the errorstatus.
Naming Convention is ok as long as it is consistent.
Required data dictionary and more documentation on the procedures.
Suggestion on DBDesign/performance related: Would recommend creating primary key on all tables - easy for replication in future. Clustered index on numeric column,
Avoid using identity - difficult to replicate if we are going to use replicated DB as DR.
MAK, thanks for the feedback. A few comments here:
Making the database name configurable can be done if it's needed. In my opinion this is YAGNI.Quote:
Originally Posted by MAK
It's a trade-off. As soon as you make it configurable, you have to pass it around from script to script. You can't just do something simple in a script like 'USE SqlCredit;'.
The location of the data and log files should be configurable, but it complicates the code, so I left it out. For a product, you would need to make that configurable. I will add it to the backlog.
The whole thing is built to support both SQL 2000 and 2005 for now, so I'm not using TRY/CATCH. In these simple INSERT/UPDATE/DELETE statements, there is no need to wrap the statement in an explicit BEGIN/ROLLBACK/COMMIT. For procedures that include multiple actions, I will definitely use explicit transactions.Quote:
Originally Posted by MAK
Yes, I failed to include an E/R diagram in the second article. That will be in the third. As the schema grows and changes, I will include updated diagrams.Quote:
Originally Posted by MAK
I think stored procedure documentation will create a lot of discussion, so I started a separate thread.
These tables do have clustered primary keys that use IDENTITY. I'm not completely sure what you're suggesting, but I am intrigued.Quote:
Originally Posted by MAK
You're proposing two columns, one for clustering (int - populated how?) and one as the primary (non-clustered) key. I assume you're talking about a UNIQUEIDENTIFIER for the primary key using NEWID() or [if we were supporting only SQL 2005] NEWSEQUENTIALID().
Do I have that right? It's an interesting option because it gets around the standard "don't use GUIDs as primary keys" argument because you're clustering on an [assumed ascending] integer.
Can you elaborate on this?
Thanks.
GUID is not necessary. Any insert or update or delete should be done thru procedures. You could have a table that maintains all the max value of each table and based on the max value you could add +1 before inserting and update the single table.
In my experience, identity is a pain when it comes to transactional replication. Atleast in the next version, identity property would be replicable.
ER diagram alone doesnt help in understanding the functionality. Data dictionary is also needed which explain the reason and function and relation of every column in the table. It also explains the functionality of the storedprocs and the parameters used. I would be glad to send a sample of database dictionary.
This is an interesting option. We (Corillian) have done this in the past but moved in recent years to using IDENTITYs.Quote:
Originally Posted by MAK
An interesting point here is that we had people challenge us on the scalability of just the solution you mention. Short answer? We proved that it scales just fine.
This is a valid point. It leads many people to select NEWID() for their PKs, but that brings a bunch of other issues with it.Quote:
Originally Posted by MAK
I have written data dictionaries and find them very valuable. There are a number of great tools available.Quote:
Originally Posted by MAK
To make one work requires a commitment by management that an updated data dictionary is a normal part of the deliverables for a project.
I prefer ObjectAction naming, but as someone else mentioned, the real important thing is to have a standard and stick to it. A standard naming convention should be included for ALL objects.