Chrissy321 (2/11/2013)

---

I have never been a tbl-er but I have been a usp-er.

Lately I put everything in a schema. Is this a reasonable statement as far as a best practice? Always put all objects in a schema besides dbo.

Educational comments most welcome....

Also, it's extremely inconsistent unless you also name all your tables tblXXX (and DO NOT, EVER, do that, ).

RonKyle (2/13/2013)

---

Why would you never do this? I've seen dimension tables prefixed Dim and fact tables prefixed Fact. I use dtbl and ftbl. With my tables, I have tbl, tlkp, trel, xtbl and stbl. I find this very useful when going back to the design long after it's created to understand what I've done.

As for the stored procedures, I'll use add_, tfm_, sel_ and others. Again, this helps me remember it's purpose when going back to it later.

I don't have many views, but it helps to have at least vw_ if only to distinguish it from tables.

I've been doing this a long time and seen the benefits. Without some real factual reason, I don't think it's a good idea to tell someone to never ever use the system.

[b]Andy Hyslop (2/11/2013)[/b
...
Why should User Defined Functions not be prefixed UDF or udf, views not Prefixed with VW or vw and Stored Procedures not USP or usp
...

Grant Fritchey (2/13/2013)

---

Ever is probably an over-reaction, but in general these types of naming conventions just don't make sense. How is tblInvoices easier to understand than Invoices? How is it more clear? Further, when you get hit by a bus, what does it communicate to the next person who takes over your job. I just do not see it.

I'll give you an exception I have used and that's for lookup data. You know, the little name/value pair tables that are just there to enforce select lists? On those I've used c_TableName. The 'c_' stood for Code tables. It served two purposes. It made them all group together within SSMS, and it differentiated these tables from real system tables.

However, now, I wouldn't even do that. I'd put them into a separate schema but with standard names. It would serve the same purpose but improve the overall readability. Instead of c_ which means nothing unless I tell you what it means, I could use Code.TableName or Lookup.TableName which makes things more clear.

Naming standards should improve clarity and communication. Specialized abbreviations just don't add to the clarity. In fact, they tend to reduce it. But, this is one of those things that comes down to preference, not a technical reason. So if it works for you, go for it. But it's not a standard I'd support given the choice.

Michael Valentine Jones (2/13/2013)

---

That is an odd question to show up on a SQL test. There are no technical reasons (that I know of) why you can't do that, or why the application would work better.

I have seen many applications that used UDF, VW, and USP, but I have never seen any actual application problems caused by this.

Of course there are many good reasons not to use them that others have pointed out, but in the end they amount to style preference, standards, ease of coding, ease of understanding, etc.

I'll probably have every DW expert in the world yell at me because it violates status quo naming

That, not withstanding, the key for everyone, whether it's good or bad in their opinion, is to follow the "standard" used in whatever shop you're working in

Jeff Moden (2/13/2013)

---

On the other hand, I don't necessarily want all of those lookup tables to group together. I'd rather have them group together with the tables they support. Although I hate the idea of using Hungarian notation to identify the object type, I'm all for relational prefixes that identify the main process they're associated with. For example, if I'm dealing with loans, I'll have a table call "Loan". I'll have a lookup table for loan type and rather than calling it c_LoanType or putting it in a separate schema, which will appear quite the physical distance from the Loan table in the object explorer in the size databases I work with, I'll call the table "LoanType" so it's very close or maybe even adjacent to the "Loan" table in the object explorer. I also have an audit table for the loan table. Rather than grouping all the audit tables together by using the "Audit*" naming convention, I call the table "LoanAudit" so that you can easily tell there's an audit table for the "Loan" table just by having the same leading prefix as the main table name.

Shifting gears, there are two schools of thought. One is to prefix object names using Hungarian notation and, trying desperately to not come across like Joe Celko, I just see no merit in that especially since things can change. The other school of thought is to group names by the functionality they have according to functionally related areas (Loan, LoanType, LoanAudit, Customer, CustomerType, CustomerAudit, for example) and THAT type of prefix makes a lot of sense.

There's also more than one type of database which adds to the "It Depends" notion of prefixing. I can easily see how object name prefixes, such as "Dim" or "Fact", would be extremely helpful in a data warehouse. The problem there is that some people overdo it IMHO. For example, it's not likely that I'd name a general purpose Calendar table "Dim_Calendar". Maybe "Util_Calendar" but not "Dim_Calendar". I still lean away from using prefixes of such a nature though because they would physically separate (for example) related tables such as "Fact_Loan" from "Dim_LoanType". I'll probably have every DW expert in the world yell at me because it violates status quo naming conventions but if forced to identify a table as either a dimension table or a fact table, I'd rather use suffixes so that all the (for example) Loan related tables are physically grouped together in object explorer no matter if they're Dim or Fact or something else. The exception to that rule would be general purpose utility tables that would get the "Util" prefix or live in the "Util" schema for obvious separation.

With the possible exception of Hungarian notation (again, just my opinion but should never be used), it truly "Depends". That, not withstanding, the key for everyone, whether it's good or bad in their opinion, is to follow the "standard" used in whatever shop you're working in. Doesn't matter whether you're an FTE or a consultant, follow what is currently in place for a given database because consistency if very important is naming conventions.

Again, just expressing an opinion here.