How I use schemas.

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

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

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.

Clear and simple explanation Alexander, thanks for sharing!

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

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?

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 ?

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.

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

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

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;

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

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.

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.

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?

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.

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!

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.