Having a look through the new features of SQL2008, I was intrigued to find the new Declarative Management Framework. In short, it's a way to enforce various policies on your environments.
Without going too far into it (in this post), it made me think about what kinds of policies or standards I like to enforce as a Team Leader.
One particular area is in object naming conventions. This is always going to be an idiosyncratic/personal aspect of programming, but I’ve found that if one person takes the initiative, other people will follow. An agreed standard makes it easy to predict what an object will be called, cutting down on searching and guessing when you’re not familiar with a schema that someone else has designed.
Tables:
- No tables have a plural name, i.e. “Lottery.Entry”, not “Lottery.Entries”
Stored Procedures
- have the prefix “s”
- wherever possible include the main object they reference, what they are doing to the object, and by what parameters (if any, and only when there’s only one or two, otherwise use “filters” or “various” etc.) in the format “s[OBJECTNAME][ACTION]by[PARAMETERS]”. The main point is the object name and the action. This is open to a fair bit of interpretation, but here’s some examples
o Lottery.sEntrySelectByID
o Lottery.sEntryInsert
o Lottery.sResultSelectByFilters
o Lottery.sPredictionUpdate
- no underscores. One of my bugbears is guessing whether or not an underscore might separate one part of an object name from another. The simplest answer is to not use them
Views
- have the prefix “v”
- if they return fields from one table only, then they are named “v[TableName]”
- it they return fields mostly from one table, and reference other tables/views in order to bring back names etc,. for IDs in the original table, then they are named “v[TableName]Overview”
- no underscores
Triggers
- prefixed with “trig”
- suffixed with “AfterInsert”, “InsteadOfInsert” etc
- example: Lottery.trigEntryAfterInsert
Functions
- same rules as for stored procedures, but prefixed with “f”
Keys
- prefixed with “pk” for primary, “fk” for foreign, “uq” for unique
- perhaps “uk” would be more consistent for uniques? Hmmm I may have stuffed that one up over the last few years….
- reference the object name and the fields: e.g. fkEntry_LotteryID
- hey what’s that underscore doing there? I’m fine with underscores in objects that are rarely referenced which writing code off the top of your head. I think they’re fine in keys, indices etc., anything that’s not a view, proc or table
Indices
- ixTABLENAME_FieldName1_FieldName2 etc
- no need to reference INCLUDEd columns
Constraints
- “df” prefix for default constraints, e.g. dfEntryName
- “ck” prefix for Check Constraints
I’m sure there are plenty of people who would vehemently disagree with some of the choices I’ve made above. That’s great, and I’d love to hear why! My main point though is that having standards helps save time by making it easy for fellow developers/DBAs to predict what your objects are called when they’re searching for them. In a database like the main one I manage at the moment, where there are now over 10,000 objects, this can save some of the precious sanity we have left.
3 comments:
I'll disagree with you! But only on the semantics of the naming standard. As far as having a standard goes - pick one and stick with it. Naming standards are great because you always know what something is, as long as people follow the standard.
Typically I enforce plural in table names, mainly because I use the table to store more than one of something and any single row represents a single instance that collection.
I never use prefixes. Ever. It makes me feel like I'm back in 2001 cracking out Perl code on my awesome IBM Modem M keyboard. I could hover with my mouse and know what type of object a database object is, but frankly I shouldn't care. I say that I shouldn't care because I use a naming scheme similar to yours where stored procedures all have an action verb in their name so I know better than to SELECT from them. Views are, in effect, tables so I shouldn't care if it's a view. Functions are tricky, I also try not to use them unless it's absolutely necessary and, when I do I make it very apparent and use a name that is indicative of the type of data being returned (e.g. GetSitesForUser vs GetSiteById).
Indexes, keys, constraints: I do something very similar
UX - unique
IX - non-clustered
CX - clustered index that isn't a PK
But really, who cares how the naming scheme is put together just so long as people are it? The fact that people have them at all is far more important that the semantic, moderately annoying, details of them.
Awesome, I appreciate that you've left such a well thought out reply. Thanks!
One thing that makes it important for me to be able to quickly distinguish views from tables is another standard that I use: that stored procs never directly reference tables, they always hit views. Why? Business rule enforcement. Perhaps a topic for a future post.
My reasoning for putting the object name at the front and the verb at the middle/back is grouping of objects in alphabetical order. All the procs that affect the Lotto.Entry table are grouped together.
But hey, I'd happily follow the standards you've outlined in your shop as well!
I, personally, am a big fan of plural naming for tables - but any standard is better than none at all.
I do not like to prefix views simply because I think of views as a layer of abstraction - and if for some reason the view needs to be materialized as a table instead of a view or indexed view (it hasn't happened yet, but I'm sure some day it will), you then need to modify your code in order to remove the V from the beginning of the object name - unless you name the table the same as the view, but you are then going against the naming standards.
I really enjoy standards discussions. Everyone has an opinion, and those opinions are most often different from others. As long as the agreed upon standard is being enforced it doesn't really matter what the standard is (for the most part) as long as it is intuitive to the developers and support staff and makes life easier for others coming on board.
Post a Comment