January 23, 2010 at 12:23 pm
Comments posted to this topic are about the item We'll go no more a' tibbling
Best wishes,
Phil Factor
January 24, 2010 at 11:15 pm
I don't mind tibbling. I've worked with and without it. It makes it easy to classify objects especially in large queries, Example: is that object the whole table or a filtered result set (view)? Object names (tables, SP, views etc) are OK but column names is going a bit far, as mentioned in article these sometimes grow or change.
January 25, 2010 at 1:17 am
I don't mind prefixes when it gives an indication of the objects purpose. For example, a [d] prefix for data tables, [ref] for reference data. Procedures like cussp_GetCustomerById, or addsp_GetAddressForCustomerId tell me immediately that they are customer and address procedures - and with the prefix they are logically grouped together in SSMS. What I hate is opening a database and finding all procedures prefixed [sp]! There is little point in that I think.
January 25, 2010 at 1:26 am
Phil,
I frequently hang on your every word. This is a place where I disagree. I am a dreaded developer, but I am also a part-time SQL DBA and have been for many years. I've NEVER done a serious conversion from MS Access to SQL (as your Editorial suggests), however, I have always been, and remain somewhat of an adherent to hungarian notation...
Microsoft has backed off of it, and for good reason, in some areas, but I don't totally agree with their retreat. Hungarian notation had it's place, and whatever the replacement might be, it will have its place. In general, I don't religiously use Hungarian notation for variable names OR [especially] column names (when you see it in column names, that is, to me a clear sign of a database ported from MS Access to MS SQL!).
I do believe that most DATABASES (something most people don't do!) should be prefixed with a "db", most tables should be prefixed with a "tbl" and most stored procedures (which I won't personally do db CRUD without) should be prefixed with "sp". Sticking to this standard makes things so much easier for full time developers like myself to try to track down issues, especially in XML generated by .NET and in VB projects written before .NET existed. (You don't have to search for every "Vendor" occurrence, but only the "tblVendor" occurrences!).
I would NEVER prefix column names however, but I've certainly "lived" in environments where that was common -- due to MS Access roots....
On top of that, on a typical day, I work on 3-6 different clients' systems. One of my better clients had someone that hadn't heard of any of these things, or at least filtered them through too many people before implementing them... Instead I have poorly named [hard to find] databases with similarly named [hard to find] tables. Additionally, to follow the lead of Microsoft, _I guess_, they prefixed ALL of their stored procedures (well over 500) with sp_... We all know what that means....
Anyway, that is my 42 cents. Sorry so long, but I do have some strong opinions here. I predominantly develop in C#, and I really don't use Hungarian notation in most of that code (unless I really think it will help document that code for the next guy.
January 25, 2010 at 1:32 am
I only really use tibbling on views and stored procs names and have moved right away from it on tables. My application development work is totally through stored procs and the .Net data access layer now so I never refer to the tables directly in program code but to datatable objects within the dataset which are built from often complex select statements referring to multiple tables. In T-SQL short aliases are often used for table names anyway, as column references need quantifying with the table name.
I use various prefixes on stored procs to denote their type eg dap_ for data access layer CRUD) procedures, lp_ for lookup tables procedures, rp_ for procedures used solely for reporting and up_ (user defined proc) for the leftovers.
I really hate tibbling on column names. One database we manage, written by a long departed contractor, has total tibbling and is highly normalised but has no database stored relationships, so that you have to remember that intTitleFK_ID relates to key field PKintTitleID in TblTitles just to get Mr, Mrs or Miss - the name difference in the columns means nothing will link automatically and various int columns have been changed to varchar over the years but not been re-named 🙁
January 25, 2010 at 1:46 am
I couldn't agree more with P Jones' comments -- I realize I didn't really address the "tibbling" question well, but rather went at it from a developer's point of view.
I like "tibbling" (a new term to me, actually) with regards to databases, tables, views and stored procs, but NEVER in terms of columns. I think P[aul?] said it well in those terms, with regards to us .NET developers, however, he missed a couple of points with regards to someone like me...
I generally have to develop the _best_ code I can without the benefit of a DBA -- I am the DBA. So I run query plans (when something is slow or _potentially_ slow), and work from those. I DO NOT let .NET dictate those sorts of things. I'm too familiar/close to the DB (since I do it all) to trust a "generator" to do it all for me...
January 25, 2010 at 2:08 am
I frequently hang on your every word. This is a place where I disagree.
[p]Please feel free to disagree. Tibbling has somehow become a practice in data naming with SQL Server without much discussion, and it is nice to have that discussion with all views represented. Don't mind me. I'm just putting forward the case for the prosecution. It suddenly occurred to me after writing the editorial that the entire process could be automated. One button for 'Tibble my database', another for 'Untibble my database'. Wake up, 3rd party Tool Providers![/p]
Best wishes,
Phil Factor
January 25, 2010 at 2:48 am
dhamilton-905368 (1/25/2010)
...most stored procedures (which I won't personally do db CRUD without) should be prefixed with "sp"....Additionally, to follow the lead of Microsoft, _I guess_, they prefixed ALL of their stored procedures (well over 500) with sp_... We all know what that means....
Glad you cleared that up 😀
I tend to prefix all mine with "usp" just to differentiate... six and two threes really but I just like to stay away from "sp".
Agree with everything else you said though 🙂
I'm from a very similar background and still tend to use tbl prefixes. One project I'm currently working on is a legacy database - all fields are prefixed with str, lng (spot the access conversion!), etc... actually helps to some extent when writing ORM classes in .Net, but looks kinda hideous and, as previously mentioned, these types can change... so I'll stick to tibbling for table names, stored procs and udf's, but strongly avoid it for field names ("fiddling"? :-))
January 25, 2010 at 3:30 am
dhamilton-905368 (1/25/2010)
I do believe that most DATABASES (something most people don't do!) should be prefixed with a "db"
Why?
most tables should be prefixed with a "tbl"
Why?
and most stored procedures (which I won't personally do db CRUD without) should be prefixed with "sp".
Why?
If there's a piece of SQL script that reads
USE Travel
EXEC dbo.ScheduleTravel
is there any doubt whatsoever that Travel is a database and ScheduleTravel is a stored procedure?
Can you USE a view or EXEC a database?
All I find that prefixing stored procedures does is make it harder to locate them and navigate to them in Object Explorer
Tables and views are worse. Let's say I have a large table that's been called tblClientTransactions, then, due to volume or new development it becomes necessary to split the table into 2. The standard trick would be to create a view with instead of triggers with the name of the table to present an unchanged interface to any queries that need to see the table as it was (especially if the queries are embedded in the client app). Now I have a view named tblClientTransactions. Hmmmm....
The only thing that I use prefixes on are indexes, primary keys and unique constraints. Why? Well, two reasons. They never appear in code (or at least shouldn't) and so the more complex name is not a burden anywhere. So I can see, when looking at a query's execution plan whether the index used is just an index or if it's one of the constraints as the only thing that the exec plan tells me is whether or not the index is clustered
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2010 at 3:34 am
Hi all.
I'm one of those developers that write front ends to databases, and usually end up doing the database design as well.
I used to code in VB and used Hungarian for that exclusively. BUT with my database design hat on I NEVER used hungarian for column names or anything like that.
The only prefix I allowed myself was for logical grouping of stored procs (a la customer, address examples above) and why would I need to include 'sp', it should be obvious that I'm calling a stored proc from the program context. You do separate your data access layer don't you?
I started back in the day of btreive databases with maximum 8 character column names - yuck. The freedom from this restriction with first ACCESS and then SQLServer was wonderful. I tend to name my columns for what they logically contain. A bit column may be something like 'isAccountHolder' in a customer table, a money column might be 'maxCredit' in the customer account table.
Now we can start the argument about camel case etc. but it suits me as I now code mostly in C# and this makes for easier matching to objects - but heck you can use ToUpper() in code if you want to.
Bill
January 25, 2010 at 3:49 am
Gail
I agree with you wholeheartedly. The only concession I would make would be UPPER CASE for table names - but that is more a matter of style and preference rather than vital for understanding the context or helping with coding.
January 25, 2010 at 4:28 am
in databases where i have to make a front-end vb.net application, i find it easier to tibble the column names. but maybe this was because i was under the dreaded visual studio.net 2002-03. i seem to remember having to correctly designate the vb type for the type of sql data it was supposed to retrieve.
i am not a religious fanatic for one type of language or programming style, i often cull the best of whatever there is; or for my multi-role case, whatever keeps me from tripping over every 2-3 years or so.
January 25, 2010 at 4:33 am
I would rather a table named tblCustomer than one named A72GZF (or something equally nonsensical). Quite the opposite, I tend to be somewhat verbose when naming objects. Sure, there's a little more typing but think of all the saved trips to the data dictionary to find out exactly what table A72GZF is for. Having said that, I'm not fond of column names prefixed with the datatype. That's just lazy.
James Stover, McDBA
January 25, 2010 at 4:41 am
Ok there's no way I am prefixing database names with "db", no way I'd prefix column names. But views, functions and procs yes. Tables, well I don't think there's a real need... but if the project requires it, then hey ho. The biggest issues arrise where one or more standards are used within the same database, which does look crud.
GilaMonster (1/25/2010)All I find that prefixing stored procedures does is make it harder to locate them and navigate to them in Object Explorer
If they've all been prefixed with the same "tibble" then the order would be the same with as without. It's only if mixed tibbles are being used where you get issues.
Tables and views are worse. Let's say I have a large table that's been called tblClientTransactions, then, due to volume or new development it becomes necessary to split the table into 2. The standard trick would be to create a view with instead of triggers with the name of the table to present an unchanged interface to any queries that need to see the table as it was (especially if the queries are embedded in the client app). Now I have a view named tblClientTransactions. Hmmmm....
Now that's a clever trick I hadn't though about... but then the dev's would now also need to know which are views with triggers on.... Hmm not something easily seen at a glance of a name.
Personally I do like to know the difference between a view and a table. If it has a table has to be split and a view placed above then the app code should be changed unless the app code uses stored procedures. I don't like app's doing table level stuff... but unfortunatley where I work, this is common place.
At the end of the day each to their own, as long as they follow the design laid out when working in someone else's sand pit.
January 25, 2010 at 4:44 am
the content inside a can, cannot know the content outside the world. 😀
intEmployeeID vs. EmployeeID, nvarcharCustomerCode vs. CustomerCode ?
mine is based upon a variety of experience, but i do not expect all experiences to be the same, in which case, passing judgment at once is cause for comment.
Viewing 15 posts - 1 through 15 (of 77 total)
You must be logged in to reply to this topic. Login to reply