THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

How I use schemas.

I use schemas to simplify granting permissions. For tables and views, I have three schemas:

  • Data, the actual data my customers need. Can only be modified via sprocs.
  • Staging, only visible to data loaders and devs. Full privileges on INSERT?UPDATE/DELETE for those who see it.
  • Config, the configuration data used in loads, only visible to data loaders and devs. Can only be modified via sprocs.

For sprocs/UDFs I have the following schemas:

  • Readers
  • Writers
  • ETL
  • ConfigReaders
  • ConfigWriters

Also I have dbo schema for the objects that do not belong to any category, but there are less than 20 objects in it.

Roles are granted privileges on these schemas.

This approach saves me a considerable amount of time, because I do not have to grant privileges to each object individually. That would be a very tedious and error-prone process. I have hundreds of objects which need exactly the same set of privileges, and doing one and the same thing over and over again is not really efficient.

We never had any problems with this approach.

One more thing: I think that in most cases developers, not DBAs, should decide which schemas to use, because developers should know the subject area better.

This is a reply to posts by Brent Ozar and Buck Woody
Published Tuesday, May 18, 2010 2:22 PM by Alexander Kuznetsov
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James Luetkehoelter said:

I agree Alexander, with one caveat "I think that in most cases developers, not DBAs, should decide which schemas to use, because developers know the subject are better". I would agree would that if 1) The DBA was the proverbial "Production" DBA and 2) the developers use schemas specifically for permissions sets (not naming convention, which seemed to be Brent's biggest objection, which I agree with).

I'm in the camp that the DBA really needs to have his foot on both sides of the fence and should know the data better than anyone - the actual information. I may be in the minority, but that's my approach. And all too often I've run into developer groups that jump on schemas because they either started in Oracle, or just like to use them to organize stuff...

But otherwise I try to use the same approach you've described...

May 18, 2010 3:47 PM
 

Alexander Kuznetsov said:

James,

I think that in most cases developers know better what the customers need, because the developers are less isolated from the customers. Why should a DBA, who rarely has a chance to communicate with end users, have a say in how to name columns or which schemas to use? What do you think?

May 18, 2010 4:07 PM
 

noeldr said:

>>>Why should a DBA, who rarely has a chance to communicate with end users, have a say in how to name columns or which schemas to use? What do you think?

because then you get to see columns with names like

[% yield] or [timestamp]

or schema named [event], [merge] ...etc

Yeah DBAs should have a say ;-)

May 18, 2010 5:30 PM
 

Alexander Kuznetsov said:

Noeldr,

My customers want to see columns [open],[low],[high],[close] in my result sets. Why not name them the way they want them to see? I have columns in my tables named exactly as my customers want to see them, which simplifies development and maintenance. What is wrong with it.

Why should my customers care which words are reserved in T-SQL?

May 18, 2010 6:25 PM
 

Adam Machanic said:

Agreed 100% with Alex. Some production DBAs seem to get needlessly caught up in their idea of "best practices", forgetting about the realities of development deadlines and end-users. The right answer is to leave this to neither "devs" NOR DBAs, but rather to people like Alex who are (correct me if I'm wrong, Alex) "database developers". Developer-minded people who understand the database. I'm proud to count myself in that group.

It is my opinion that DBAs should concentrate on backup, recovery, DR, and related administrative issues. If you want to get involved with the data, become a database developer. More often than not, those who try to do to both end up without much skill in either concentration.

May 18, 2010 8:58 PM
 

dugi said:

Clear and simple explanation Alexander, thanks for sharing!

May 19, 2010 5:39 AM
 

Alexander Kuznetsov said:

Adam, yes, you are correct, I am a developer with some understanding of RDBMS, and not a DBA at all.

May 19, 2010 10:13 AM
 

James Luetkehoelter said:

Adam, Alexander -

Actually I agree with what you're saying. The problem I run into is that there is often a huge dscrepency out there with a developer and someone who is actually a database developer (as Adam describes it). And yes I agree that there are DBAs out there that get consumed with standards and "best practices" (I really hate that term).

I think I might just dump the word DBA and call myself a database professional :) or amateur perhaps...

May 19, 2010 10:40 AM
 

Alexander Kuznetsov said:

James,

I used to call myself a database developer, but now I am just a developer. You know, our client side languages rapidly improve all the time, and T-SQL does not keep up. So there is less and less incentive to choose T-SQL over modern languages.

May 19, 2010 11:25 AM
 

Adam Machanic said:

Alex: You can be a database developer even if you don't code in T-SQL all of the time. I consider a database developer to be someone who does several of the following as a primary job function:

- Designs databases

- Creates data loaders or ETL processes to populate said databases

- Designs and codes data access interfaces (be they stored procedures, Entity Models, or whatever)

- Designs, codes, and exercises data and data access test plans

- Engages in performance tuning at the query and/or data access interface level

In other words: You understand the data, how it's used, and where it's going. You understand the role of the database and the role of the application, and you're developing code. You're not generally concerned with backups, restores, or monitoring at a much higher level than that required to keep the application up and running.

What do you think?

May 19, 2010 3:28 PM
 

noeldr said:

Alex,

You have created an easy example to represent your purposes. Forget about "best practices".

Simple question (and this goes for Adam as well):

What would you do if your customer wants column names with spaces in them ?

May 19, 2010 3:36 PM
 

Alexander Kuznetsov said:

Adam,

I like your definition. I would also add unit testing and stress testing, but it is probably just me.

Noeldr,

I have not "created an easy example", I described a real system which went live some years ago and runs without any problems.

What "if your customer wants column names with spaces in them ?"

I would:

1. Say that I have never done this before and I can either do it right away with underscores instead of spaces, or I need some time to research.

2. If the customers requests some research and is ready to pay for it, I would just research if something can break. Naturally the DBAs will be involved in determining if something breaks. However, IMO well designed modern tools should handle blanks in folder names, file names, table names, and column names - many customers like them and want them.

What do you think?

May 19, 2010 5:28 PM
 

Adam Machanic said:

Alex, I assumed unit, stress, data quality, and any other kind of testing when I mentioned "designs, codes, and exercises data and data access test plans".

Noeldr, it depends on the situation. If it's a reporting database to be primarily used via an ad hoc point-and-click reporting tool then I might argue a bit, but not too much. If on the other hand people will be writing queries using a text editor I would argue more heavily given the annoyance of quoting everything. Conceptually I don't have a problem with spaces in object names. There is a reason the product supports them. I just find them to be annoying.

I actually had a thread on Twitter about this a while ago. I thought that it might be interesting to stop naming CHECK constraints using names like "CK_PriceAmount" and instead start doing something like:

ALTER TABLE products

ADD CONSTRAINT [Price must be between $10 and $100]

CHECK (Price BETWEEN 10 AND 100)

This way when the constraint is violated it's quite easy to figure out what went wrong. I mentioned this on Twitter and received a number of responses. The one that really stuck in my head was from some DBA who said something along the lines of "not a horrible idea, but you need to replace those spaces with underscores. I would NEVER allow ANY name with spaces in my database."

... I thought that position to be extreme then, and I still think so today. Especially with something like a CHECK constraint that is effectively set and forgotten--not actively referenced by end-users in queries, etc.

May 19, 2010 5:37 PM
 

Alexander Kuznetsov said:

Adam,

I am actually using descriptive constraint names such as CHK_InventoryLog_NonnegativeCurrentQuantity. IMO it does not really matter if constraint names have blanks or underscores as long as the name is clear to understand.

May 19, 2010 6:03 PM
 

Alexander Kuznetsov said:

Adam,

I ran a simple smoke test, I created the folloiwng table:

CREATE TABLE dbo.[test 1]

   (

     [test col1] INT ,

     [test col2] DATETIME ,

     testcol3 INT

   ) ;

GO

Then I refreshed suggestions in SQL Prompt and had no problems typing queries:

SELECT FROM dbo.t

and saw test 1 in the list of tables. I selected test 1, added *

SELECT * FROM dbo.[test 1]

then I hit Tab to expand *, and here we go:

SELECT [test col1] ,

       [test col2] ,

       testcol3

FROM dbo.[test 1]

No problems so far.

May 20, 2010 9:23 AM
 

noeldr said:

Alex and Adam,

Again this is just some food for thought. I do have flexibility in terms of allowing certain things on my environment by the way I am a DBA :-). I work in a mixed environment *NIX and Windows where most processing happens on the NIX side (think of it as Perl, Python, bash, java ... groovy, rails, etc)

spaces in those heavily parsing scripts engine are a Time-Bomb :(

This is by far not just the only problem with names, Support for many open source stuff get ugly, code generators (tsql or any other client-side dynamic language) have to be adjusted, etc.

I do try to be as flexible as you describe but I do think a DBA input is important (up to a point of course) and should not be disregarded simply because we don't tend to face the "clients", after all in my experience when something does not work it tend to be called "a database problem" until proven otherwise ;-)

May 20, 2010 3:12 PM
 

Alexander Kuznetsov said:

Noeldr,

When you mentioned Linux you essentially won the debate ;). Since Vista was launched this is a trump card in such discussions. I can guarantee that a mere hint that this might not integrate with Linux will scare any customer I know of out of the idea of column names with spaces.

Note that I would definitely consult the DBAs in case when I am not sure if it can work, so we actually do not disagree.

Still, "schema named [event], [merge]" should go through JTDS driver without a glitch, correct? What can go wrong with such schema names?

To my best knowledge, columns named [open] and [close] have no issues with JTDS.

May 20, 2010 3:46 PM
 

noeldr said:

Alex,

You are correct. jTDS does handle those cases successfully.

It is all the parsing that goes on that some times do not quote those when "generating" queries and admittedly they could have done it in a a more robust way.

Anyway, these debates are what makes us all better because seen other peoples perspectives is really great. I have certainly learned a lot from your posts :-)

May 20, 2010 5:50 PM
 

Paul White said:

DBAs and developers should work together on names (and most things come to think of it).

Makes sense to me to give developers first choice, and personally I encourage both sides to use human-friendly names.

I have used spaces in columns, index names, constraints and so on since at least SQL 7 ;)

Discussion += $0.02;

May 21, 2010 9:28 AM
 

Alexander Kuznetsov said:

Paul,

To you have Linux/UNIX clients reading your data?

May 21, 2010 9:38 AM
 

Paul White said:

Hey Alex,

No - at least not in any current systems I deal with.

To clarify: I tend to use underscores in column names by default (mostly out of habit) but I have been required to use spaces at some sites, and occasionally it has made more sense to do so.

I do default to spaces in index and constraint names, for example.

Paul

May 21, 2010 9:57 AM
 

Brian said:

I'm very surprised by what I'm reading here. My surprise has nothing to do with schemas. Instead, I'm baffled by the conversation about column names. What kind of "customers" ever encounter actual column names? Do the "customers" have direct access to tables? If not, then what prevents using column name aliases in stored procedures or views to satisfy specific naming requirements? Even with that, I'm curious about what kind of "customers" care about column names in a result set. The column names typically do not go beyond application developers, who put whatever names might be necessary on GUI forms. Using actual column names that require brackets in SQL coding seems like a *huge* pain in the butt for everybody who writes code against the database. It affects anybody writing ad hoc data manipulation scripts, it affects anybody writing ETL scripts, and it affects anybody writing stored procedures or views. In addition, I think the brackets make the SQL code look extremely ugly to read.

May 22, 2010 8:34 PM
 

Alex K said:

I recall a while ago there were similar heated debates about spaces in folder and file names.

Anyway, there can be quite a few situations when customers get at least some exposure to underlying tables. So, whenever we encounter an unusual requirement, it makes sense to figure out what causes them before rushing to a conclusion, what benefits are they expecting from it etc. It also makes sense to re-estimate the costs of the requested feature in the current environment, rather than reuse some old decision made like a decade ago.

In many cases it is preferable to present the customers with the costs associated with the feature they request, and have them make the choice, rather than assume that we always know what the customers need.

BTW, because we no longer manually type T-SQL, because we generate much of it, and use SQL Prompt, square brackets just get there automatically without any effort at all. So there isn't much cost in bracketing column names any more, as long as you have invested a few hundred bucks in a modern tool.

May 23, 2010 2:35 PM
 

Brian said:

Unless people start programming as much with folder and file names as they do with column names it's not a valid comparison.

Please provide an example of when customers get exposure to tables. Most people seem to think that's not a good idea at all.

There has been no suggestion we should not meet customer needs at all times. The discussion is about *how* to do so.

When I have to start putting brackets in *any* manually entered T-SQL, I will be sick of programming in T-SQL. How do others feel about reading T-SQL littered with brackets?

You made two different references to rejecting "old" practices in favor of "modern" practices. Would you reject an "old" practice like normalization in favor of a "modern" practice like putting relational data into an XML column?

May 23, 2010 3:23 PM
 

Alexander Kuznetsov said:

Believe me or not, there are quite a few people "programming ... with folder and file names".

"example of when customers get exposure to tables": we restore a copy of prod DB, and let the users play with it. This way their ad hoc queries will not wreck the prod server, and when they have a better idea of what they want, we can productionize their SQL into stored procs, cover them with unit tests etc.

"How do others feel about reading T-SQL littered with brackets?" I know quite a few people who have less strong opinion on programming style.

"Would you reject an "old" practice like normalization in favor of a "modern" practice like putting relational data into an XML column?"

I guess I haven't used XML columns in production, just tried it out a little bit and decided not to use.

May 24, 2010 9:02 AM
 

Donald Halloran said:

I'm currently a DBA, but I a coming from a background of 12 years as a developer, 8 of those with SQL Server as the backend, and my role shifting from GUI gruntwork and reporting to back end design and optimization.

I'm sticking with a relatively ISO11179 compliant naming convention. Underscores as separators, components are class, qualifier and representaiton terms. Eg, order_created_dt.

Why? Because the people who really need to know what's in the columns most of the time are the devlopers... especially the developers you don't even know about yet. The ones who will be using the schema later, possibly even after you've moved on to another company. A relatively strict naming convention of this sort allows easy validation against a data dictionary. For instance, a common problem in 2000/2005 is with datetime columns. Are they *really* datetimes, or just dates? I enforce clarity in the column name: the _dt suffix is a datetime. The _date suffix is a date. This can be validated against a data dictionary. I can't imagine how to programatically validate things like [% yield] in this manner.

May 26, 2010 1:32 PM
 

Ralph Wilson said:

After reading the article and the comments, I have to toss my tupence into the fray. ;-)

Re: Naming conventions

I have to come down on the side of the underscores instead of spaces and the absolute forbiddance of special characters.  

If you have a 6 character column/table name, the requirement that you add 2 characters for bracketing the name means a 33% increase in the number of characters you have to type.  Over time that ads up.  Also, the failure to include those brackets will result in execution errors but those may not happen until the wee hours of the morning and I don't know about _you_ but _I_ REALLY hate those kinds of calls. ;-)

Re: DBA's being involved in naming tables/columns/Sprocs/etc.

The kind of DBA that does nothing but worry about back ups, maintenance plans, and the other _administrative_ functions is not the _only_ kind of DBA out there.  I've recently moved into a Development DBA position (after a few decades of being a developer of one nature or another) and I can assure you that I am not isolated from the users.  I also have trepidations with regard to letting any and every developer write sprocs, design tables/indexes/etc., or, in many cases, even write T-SQL without supervision and review.  (IMHO, anyone who thinks that T-SWL is still a primitive and simple language eeds much more education in writing SQL. ;-)

Re: Users being exposed to table/column names

The only users who are exposed to table and column names should be power users who are capable of dealing with the underscores, etc., that are used in the naming conventions.  Otherwise, someone _else_ needs to be creating the queries and, IMHO, putting them into sprocs for the user to execute and those can alias the columns to suit the users' needs.  I don't expect users to ever deal with the column names in much the same manner that I don't expect themto deal with or be concerned with the Identity columns that may be in the tables.  If worse comes to worse, the closest that the vast majority of users should come to the tables is a a _view_.

re: Folder names and spaces/special characters

I am one of those who winds up doing a good bit of programming that involves folder names.  I work with SSIS packages that handle flat files.  I also pass folder names as parameters to commandline applications that then perform ETL processes.  Squirrelly folder names are a pain!

May 26, 2010 2:40 PM
 

Alexander Kuznetsov said:

Donald,

I agree that every cohesive team should stick to one and the same naming conventions. Yet I think that different teams might use different conventions as long as they are consistent within the team.

Regarding our team, because we prefer Java and C#, we name columns and tables just like we name classes and variables in OO languages:

LastModifiedDate, OrderCreatedDate.

This way we have one and the same naming convention for all languages.

May 26, 2010 3:24 PM
 

Alexander Kuznetsov said:

Ralph,

A few comments above I was saying this: "What "if your customer wants column names with spaces in them ?"

I would:

1. Say that I have never done this before and I can either do it right away with underscores instead of spaces, or I need some time to research.

2. If the customers requests some research and is ready to pay for it, I would just research if something can break."

Based on your comments, and others' comments, I would conclude that:

the result of my research is this: column names with spaces are counterintuitive. Should we go for them, we are much more likely to make mistakes, and more likely to have annoyed and distracted developers.

Dear customer, let us have column names with underscores instead - that will allow our developers to concentrate on what really matters: delivering useful features.

Makes sense?

May 26, 2010 3:28 PM
 

allmhuran said:

Since we have already shifted from schemas to a more general naming convention, I thought I'd copy in part of the TSQL standard I recently wrote for my company. I included my reasoning in the document so that people could actually argue against the standard before it was implemented (which they couldn't do if I just made assertions). Here are two relevant parts:

Acronyms, and only acronyms, should be capitalized.

Good: order_AUD_amt

Bad: ORDER_AUD_AMT, Order_Aud_Amt, OrderAudAmt, OrderAUDAmt, order_aud_amt

Why?

• In the English language capitalization is used in three places: for grammar, proper names, and acronyms. A data element name is a single token and therefore contains no grammar. A data element name is not a proper name. A data element name may contain acronyms. This rule is therefore consistent with natural English.

• All-caps identifiers hide acronyms, and all-caps is more difficult to read than all-lowercase.

• Proper case / camel case can also hide acronyms and it can be hard to distinguish between terms.

To prove this, consider a few of the possible representations of the data element name for “the net Australian dollar mark-to-market value for a set of credit default swaps”, using standard abbreviations:

1. CDS_MTM_NET_AUD_AMT

2. cdsMtmNetAudAmt

3. CDSMTMNETAudAmt

4. Cds_Mtm_Net_Aud_Amt

5. CDS_MTM_net_AUD_amt

The fifth representation is both readable and makes the parts which are acronyms easily distinguishable from the parts which are not. This rule can be consistently applied without ambiguity or at any detriment to readability.

Underscores should be used to separate terms.

Good: customer_full_name

Bad: customerFullName, CustomerFullName, CUSTOMERFULLNAME, CUSTOMER_FULL_NAME

Why?

• See the examples in the section on capitalization. The common practise of using camel-case or proper-case to separate terms produces the horribly unreadable second and third representations. The third representation is also virtually impossible to automatically parse for validation against a data dictionary.

• A standard implies something we ought follow. Ought implies can, and underscores always can be used without contradicting other parts of the standard or creating problems with readability.

May 27, 2010 12:37 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement