Another Standards Discussion: Views and Business Rules

Let’s say that the business you work for has also been around for many years, and in that time loads of little rules about the data have appeared. Market A works differently to market B. This car shouldn’t be included in this list of products. When pulling together a list of shoppers, we only care about those that have credit cards.

Every time a new project comes up, a new part of a public facing website or a new module in an in-house application, new stored procs are developed. Often, these procs are written by people who weren’t around when the DB schema involved was designed, or they haven’t got all of those little rules I discussed above memorized. Who does?

This is where Views can come in handy. Instead of building each of those annoying little rules into each new Stored Procedure that’s developed, build them into Views, and point all Stored Procs towards Views instead of Tables.

And I mean ALL Stored Procs. As a standard. If you follow this standard, you can be sure that when it’s time to implement a new business rule, you can find the appropriate View, alter some code, and all the dependant Stored Procedures will now follow that new business rule.

For example, you have a table called Lotto.Entry. It records who has entered a lottery, and how old they are. Supplying the website, there are three hundred stored procedures which access data from this table. The CEO at your company decides that it’s no longer ethical to be reporting on people under 17 who have entered the lottery. If all those 300 Stored Procedures are pointing towards the table, you’re faced with moving data around, or changing all of those procs. If the procs are instead pointing towards Lotto.vEntry, that view can have a “WHERE Entry.Age > 17” clause added. Other Views should point towards this View instead of the underlying Table, so that the business rules are “inherited” across the system.

Another advantage is that the business rules are centralized. You know to look towards the basic view of a table if you want to know how its data is handled.

Pretending to delete data is another thing that can be done in views. Let’s say you want the users of the website/UI to feel like they’re deleting data, but you want an audit trail on that data, and you simply want to hide it from the users. Add an “IsDeleted” field to your table, and a “WHERE IsDeleted = 0” clause to the base view of that table. The data is then easily “undeleted” in cases of errors.

Views are a centralized, transparent and predictable way of implementing rules in which your data is accessed.

Object Naming Standards

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.

It's been a while!

Excuses? Work, Vietnam, Cambodia, Malaysia, music composition duties. It's been a while since I've blogged. Well I haven't forgotten about SQL, in fact it's high on the priority list.

I've just received some new SQL books in the mail. It's time to have a go at some certifications again. 70-432 and 70-433, Database Implementation and Maintenance, and Database Design, respectively. Flicking through the books, there's so much I already know, but I've never actually been near SQL2008, so there's also some huge chunks that are absolute news to me.

I'm also starting a Masters of Information Technology at the University of New England, part-time by "distance education". My aim there is to broaden my knowledge of IT, being a person who "fell into" IT at work rather than doing an IT based degree first. The degree I completed in 1995 was a Bachelor of Science, majoring in microbiology and genetics. Now I'm a DBA. Go figure! Apparently it's not such a rare thing for your degree to not have a huge bearing on your career path anymore.

Well, anyhow, just wanted to let y'all know I'm still here. Catch you soon.