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.
- No tables have a plural name, i.e. “Lottery.Entry”, not “Lottery.Entries”
- 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
- 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
- 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
- prefixed with “trig”
- suffixed with “AfterInsert”, “InsteadOfInsert” etc
- example: Lottery.trigEntryAfterInsert
- same rules as for stored procedures, but prefixed with “f”
- 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
- ixTABLENAME_FieldName1_FieldName2 etc
- no need to reference INCLUDEd columns
- “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.