January 25, 2010 at 11:05 am
I agree with the article that this process adds nothing of value. There is no confusion between objects and prefixing them with object type only makes it difficult when searching. I have databses with thousands of tables, where most of them have the same profix so imagine doing a visual search having to ignore the first 3 characters.
Francis
January 25, 2010 at 11:07 am
Phil Factor (1/25/2010)
For quickly finding database object according to their category, use, or object type, you might find the extended properties invaluable, especially if you use the sys.extended_properties catalog view. You could even do your tibbling in an extended property rather than as a prefix to the object name itself. I do agree that prefixes are handy for cutting down on long lists in something like SQL Prompt, though.
You can also use schemas for this purpose.
January 25, 2010 at 12:19 pm
Grant Fritchey (1/25/2010)
I think a lot of this, for me anyway, is just habit. Is there a good reason any more to put idx or ix at the beginning of index names? Nope. But I keep doing it because... I'm creating an index and as I type it, I put ix at the beginning. ...
I used to use a three-letter code that indicated the type of index: IDX = default, CIX = clustered, UIX = unique, UCI = unique and clustered. Made it easy to see what's what at a glance. Later, I realized that I never actually used this feature for anything, and stopped bothering with it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 25, 2010 at 12:26 pm
When I coded in Fortran and COBOL, every bit and byte had a measurable impact on the resources available to your program. Because of the scarcity of those resources, I was willing to tolerate a lot of convoluted naming standards.
However, once I progressed into database programming, I grew to abhor any naming standard that obscured the full and true meaning of the named object. I'm not saying that tbl'ing obscures an object name. I've just grown to prefer a name like customer_preference over tblCustomerPref. It's just somehow more meaningful and understandable to me.
Good post!
-Kevin
Twitter @KEKline
More content at http://KevinEKline.com
January 25, 2010 at 12:29 pm
Phil,
I'll agree with your thoughts here. But actually, my post is off-topic...sort of 🙂
In addition to being a SQL developer, I'm also a folk-musician. To what song were you refering? I know of many songs about Whitsuntide. And there's the whole "go no more a rovin'" class of folk songs. But I've never heard of, nor could find quickly with a google, any "go no more" chorused song that mentioned "though Whitsuntide is nigh."
I'm sure I'm one of very few in this forum that cares, but please shoot me a reference. I'm curious.
Thanks,
Tim
January 25, 2010 at 12:34 pm
I worked for a client where the IT shop used no prefix on tables except in the case of lookup tables (prefix LKP) and cross reference tables (prefix XREF). At first this seemed like a way to sort like tables together but from a developers perspective there was much more value.
Lookup tables consistantly had two columns, code and description. Cross reference tables had 3 columns. Instead of coding hundreds of master data maintenance screens, there were two. The application passed in the menu description (table name without the prefix) and everything was dynamic after that. It worked and saved a lot of time and resources. Beyond that all they used was usp prefix for stored procs.
M
January 25, 2010 at 1:24 pm
If all the tables were prefixed "tbl" I could see some of these points. But like previous posts indicate, there are other possible prefixes. I think it's awkward, for example, to name a table that supports a many-to-many relation SomethingSomethingRelationship. It seems to me that:
tblClass
tblStudent
trelStudentClass -- this tables sole purpose is to facilitate the m-m between the first two.
to efficiently indicate what the tables are doing. Lookups are tlkp and consist of a code or description, stbls are used by programs. If we could store the tables in a folder like structure, I could be persuaded to abandon it, but I like the way it keeps the tables organized.
As for stored procedures, add_Customer, sel_Customer, upd_Customer, del_Customer tell you quickly what the stored procedure does. I would stay away from sp_ as that is what MS uses for its stored procs.
And at the risk of someone telling me I've gone overboard, my databases are prefixed db, my warehouse staging areas are sa, and the database warehouse is dw. I like the grouping this causes, and that I can instantly recognzed the saDatabase is the staging area for dbDatabase.
I don't start "tibbling", however, if the existing database names aren't tibbled themselves.
January 25, 2010 at 1:30 pm
For what it's worth, I don't use Hungarian notation - unless in OO languages (do it there since it seems to be a common practice). I prefer to keep it out of my databases and stored procedures.
Prefixing a variable with the datatype seems wasteful - especially if the underlying datatypes change or coding requirements change. Prefixing a table with table or tbl seem to fall into the same category. I can see one possible use for tibbling a table - m-to-m relationships.
Tibbling a column is way overboard and can really cause some development delays if ever the column needs to change.
Keep it simple and easy to understand.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 25, 2010 at 1:38 pm
Interesting discussion. I prefer not to tibble anything but after reading all the entries, I can see how it might be a good idea to tibble view names and index names. Overall though, for objects that developers have to type out, I agree with Gail - you’ll know what that object is by it’s use (or you should, at least).
When I started work at my present company, I came across this naming convention (in a handful of tables, not all thank goodness):
Table name:This_is_my_table
Column name:This_is_my_table_int_this is_my_column
I just about fell out of my chair. I couldn’t go along with it. We found another naming convention and started using that.
January 25, 2010 at 1:43 pm
Someone mentioned the idea of prefixing with the company name. I've seen that, and it's a pain to deal with.
At my last job, every database started with an abbreviation of the company name. Every stored procedure, table, etc., in the database did the same. Didn't go so far as naming columns that way, but pretty much everything above that level. (The names also had the object type abbreviated into them, along with the company name, just to make it worse.)
Then came the day the company was sold to another company, and the name of the company changed. Suddenly, all the work that had gone into that naming convention was just so much wasted effort. And it will actually cause more confusion for future devs who have to look up, "what the heck does this prefix mean", since none of it is being changed. (If it were being changed, it would be the biggest useless refactor in the history of the company.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 25, 2010 at 2:14 pm
I'm also a folk-musician. To what song were you refering? I know of many songs about Whitsuntide. And there's the whole "go no more a rovin'" class of folk songs. But I've never heard of, nor could find quickly with a google, any "go no more" chorused song that mentioned "though Whitsuntide is nigh."
That quote piqued my curiosity as well. As best I could determine, it was a merger of different songs/poems. For example, I found:
George Gordon Byron, Lord Byron. 1788–1824
599. We'll go no more a-roving
SO, we'll go no more a-roving
So late into the night,
Though the heart be still as loving,
And the moon be still as bright.
For the sword outwears its sheath, 5
And the soul wears out the breast,
And the heart must pause to breathe,
And love itself have rest.
Though the night was made for loving,
And the day returns too soon, 10
Yet we'll go no more a-roving
By the light of the moon.
But I could not find anything about the whitsuntide except for a site that looked like it was a library and you could check out books of poetry. It had a poem "Finery for Whitsuntide", that had the first line - "Now Whitsuntide is drawing nigh...", and the author was BURGESS, Joseph from 1927. I could not find a copy of the poem though. 🙂
January 25, 2010 at 2:15 pm
GSquared (1/25/2010)
Grant Fritchey (1/25/2010)
I think a lot of this, for me anyway, is just habit. Is there a good reason any more to put idx or ix at the beginning of index names? Nope. But I keep doing it because... I'm creating an index and as I type it, I put ix at the beginning. ...I used to use a three-letter code that indicated the type of index: IDX = default, CIX = clustered, UIX = unique, UCI = unique and clustered. Made it easy to see what's what at a glance. Later, I realized that I never actually used this feature for anything, and stopped bothering with it.
I think Gail mentioned about the only use for this... when examining execution plans.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 25, 2010 at 2:17 pm
I have seen that some developers practice to name the tables with the name of the application then table name ...etc etc
January 25, 2010 at 2:20 pm
GSquared (1/25/2010)
Someone mentioned the idea of prefixing with the company name. I've seen that, and it's a pain to deal with.
I mentioned using a company abbreviation prefix as a source indicator, but only when I am adding objects that are not native to the application. Many integrations are best designed by appending objects to an existing database, and the original vendor for that application would never have knowledge of them. I want them to stand out for that reason as well as making it easier to maintain the integration.
When designing my own applications, I don't practice tibbling (I'm beginning to feel like I've known that word for a long time).
By the way Phil. Nice editorial. Thanks.
January 25, 2010 at 2:22 pm
In Amsterdam I met a maid,
Mark well what I do say,
In Amsterdam I met a maid,
And she was mistress of her trade.
I'll go no more a rovin',
With you fair maid.
A-rovin', a-rovin',
Since rovin's been my ru-aye-in,
I'll go no more a rovin',
With you fair maid.
<many more verses>
That's the version we used to sing. Then there's a whole sleugh of songs about Whitsun or that place themselves as happening around Whitsun. But yeah, I never heard a version of a "rovin'" one that was around Whitsun. Of course, even as a folk singer for 20 years, I've barely scratched the surface of all that's out there 🙂
Tim
Viewing 15 posts - 46 through 60 (of 77 total)
You must be logged in to reply to this topic. Login to reply