RE: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names

[Huppatz, Jeremy B]

Greg - appreciate you starting the dialog on this one.  My opinions on this one are strongly felt so it's hard to bring my writing style up from a conversational level - don't feel there's any sort of personal slight in what follows...
 
Multiple objects in the database with the same name?
 
*Hands a 5 year old a large box of matches*  "Don't play with these!"
 
For those of you who work with small to medium SQL Server databases that every man and his dog has free rein in...  Really think about what level of complexity you're opening yourself up to administering by taking this approach - especially if your database is just complex enough to need to lock different users (or user roles) down to different levels of table access via views.  Oh... but what if CEO Bob is a member of the Accounts AND the HR groups, and his default schema is All?  Which version of the Employees "table" does he get then if he wants to point his access database at SQL Server and jerry-rig some custom reports?  I guess we can call one of the Gregs if it all gets too confusing...
 
The prosecution rests.
 
For those of you who work with enterprise databases, enjoy your sproc and function coding - this shouldn't be an issue for you.  This is why you pay your developers and apps DBAs decent salaries, after all.  Hey Jeremy - CEO Bob wants to be able to dynamically slice and dice the Employees table... "Sure - Any high level filters - time periods, etc?  I'll write a bunch of sprocs and functions he can point his reporting editor of choice at and he can knock himself out... ahem... explore to his generous and majestic heart's content!"  *Dangles cap hopefully*
 
On the issue of prefixing as annotation of functional areas - Schemas for the win.  The less dross you add to your object names unnecessarily the better.  Easy to read is easy to understand.  Just make sure everyone who uses the database knows you're using Schemas, cos you're going to get a lot of blank looks from the "quick and dirty" crowd who are used to everything being owned by DBO, and all users aliased to DBO.
 
Cheers
 
Jeremy Huppatz 
| EDS | Australia ADU | Data Engineering SME (SQL Server/Data Warehousing) | 
| Level 10, 108 North Terrace, Adelaide, SA, 5000 | Office: +61 8 8464 1739 | Fax: +61 8 8464 2138 | Email: jeremy.huppatz@xxxxxxx <mailto:jeremy.huppatz@xxxxxxx>  | 

________________________________

From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx on behalf of Greg Low
Sent: Mon 9/07/2007 3:38 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names



#2: Avoid using prefixes on table/view names.

 

Some people like prefixes like vw or v on view names. Some even like tbl on table names. There are several problems with this.

 

First up, it's common in production systems for views to replace tables and (occasionally) vice-versa. From a user's perspective, the two should be identical. As a related example, Microsoft have learned this lesson the hard way even in SQL Server with their stored procedure naming. SQL Server system stored procedures are prefixed with sp_ and extended stored procedures with xp_. However, if you now look at the list of each, you'll find system stored procedures with xp_ prefixes and extended stored procedures with sp_. That's worse than if they'd never have any scheme because now it's very confusing.

 

The second common version of this is where tables or views are prefixed with something that relates to where they are used in the system. The reason typically given for this is to avoid having to look through massive lists of tables in management tools, etc. However, SQL Server 2005 has introduced schemas and invariably, these are a better replacement for a prefix naming convention. Schemas have the added advantage of also being a security boundary and rather than granting SELECT permission on a series of tables named:

 

HR_Table1

HR_Table2

Etc

 

You'd often be far better off granting SELECT permission on a HumanResources schema that contained tables named: Table1, Table2, etc.

 

In the example Michael gave the other day of using tmpCustomers as a staging table for Customers and tmpOrders as a staging table for Orders, even in this case, I think it would be preferable to have a Staging schema that contained these tables and that they'd become:

 

Staging.Customers

Staging.Orders

 

And the "real" tables would be something like:

 

Sales.Customers

Sales.Orders

 

It's highly likely that all the staging tables would also need the same permission settings and again, these could likely be best done at the schema level.

 

Regards,

Greg

 

Dr Greg Low
Readify | Senior Consultant

Suite 206 Nolan Tower | 29 Rakaia Way | Docklands | VIC 3008 | Australia 

M: +61 419 201 410 | E: mailto:greg.low@xxxxxxxxxxx <mailto:greg.low@xxxxxxxxxxx>  | W: www.readify.net <http://www.readify.net/> 

 

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net 



to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

<<winmail.dat>>