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

[Huppatz, Jeremy B]

*grins* Sorry Greg - wasn't trying to put words in your mouth.
 
I think the smart enterprises moving forward are going to be the ones who use an SOA layer to abstract the mechanics behind how data is stored and will store it once in only one database (that of course is backed up, log-shipped, highly available and mirrored for good measure).  SOA gives data architects the opportunity to tidy up their thinking on how we approach these kinds of things.  With a bit of luck that will get rid of most of the same table/different database stuff that you referred to in your reply.  
 
However, there's certainly an "AS IS" case where many enterprises store the same transaction in N different versions of the same table in as many as N (but not always that many) different databases, sometimes on up to N different servers.
 
I understand the justification for abstraction to views - just not in specifying the same name for each view of the data unless it's actually the same data.  But if you have multiple views returning the same data, then I'd argue it's a situation where one view will do.  I still maintain that abstracting access to tables through sprocs and functions is a better approach.  I would typically only recommend the use of views to filter out pesky information that's not really useful to users (e.g. do simple joins and strip out surrogate integer/GUID keys), to perform niladic filtering based on user credentials, or to Union multiple partitions of what are ultimately a single table at a logical level, even if implemented as multiple schema-identical tables at the storage level.  
 
With the performance tuning done to functions in SQL 2K and subsequently in SQL 2K5, I honestly believe that views as a mechanism for implementing "canned queries" and partitioning data for security purposes are pretty much redundant.  Note that functions actually FORCE you to explicitly use owner/schema names when invoked.   And functions give you support for parameterization as well. 
 
In Ugg-speak (I trained as a mechanical engineer - it's part of the curriculum for aspiring pub crawl leaders...):

*	
	Explicit naming and invokation GOOOOOD! :)  
*	
	Implicit invokation name resolution make DBA head hurt... make DBA want to hurt nasty code-rats when users come with questions about what code getting run... :(

If nothing else, explicit name usage leads to faster performing code.  Viva la New World Order! *grins*
 
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 10:23 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names



Hi Jeremy,

 

No personal slight intended. Getting people's ideas on this is *exactly* what we're after, passionate is good J

 

In general, there's no way I'd be keen on multiple tables in the database with the same name. Wouldn't have done that with SQL Server 2000 and owners, still not likely to do that in 2005 either. The *only* one where I'd consider that out of what we've discussed was for Michael's example of staging versions of real tables and even then, I'd prefer the staging tables to be in another database instead. Do I think I'll see it more and more in the future though? Yes, I do. With ever larger databases being built by multiple teams, it's inevitable.

 

Currently the main place where I make consistent use of schemas is as a container for stored procs. In that situation, I find them extremely useful, particularly in relation to permissions and functional grouping.

 

However, solid advice has always been to use two part names when accessing tables in SQL Server and if you play by those rules already, the use of schemas for tables isn't an issue. Having the table that a user accesses decided by his/her default schema after he/she has used a single part name still isn't a best practise.

As for the dude that wants to jerry rig some custom reports out of his Access database by directly connecting it to SQL Server, that wouldn't feature in my list of preferences on how things would be designed and I certainly wouldn't be designing things to make it easy for him. Most large sites I work in are weeding out such folk and forcing them back through a proc or view layer. If they don't, their world isn't pretty anyway. Same with the folk that have every user aliased to DBO.

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/> 

 

From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Huppatz, Jeremy B
Sent: Monday, 9 July 2007 10:28 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names

 

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 



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>>