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.
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.
--Jeff Moden
Change is inevitable... Change for the better is not.