Naming Objects In Your Database 7

[ESquared]

So you want to name your objects. Your tables, your views, your functions. And there are a lot of people out there who name them with what the object type is. Maybe even with more information. For example:

tblOrders
Table_Orders
sp_OrderSend (NEVER prefix SPs with "sp_" because the server will look for a system SP first!)
Proc_OrderSend
OrderStatusFunc
fn_OrderStatus
fn_t_OrderStatus

even worse:
joe_sp_OrderSend
mike_sp_OrderCancel
david_fn_OrderStatus

Then there are column names:
ID
ProductID
keyProductID
pkeyProductID
pkProductID
pkProductIDCode
frnkeyProductID
fkProductID

So does this stuff help at all? Is it useful to put the type or relationship in the name of objects? Can you leave the name off the ID column in your tables? Should you put more information in there? What does it matter, anyway, as these are mere naming conventions, right? Just pick something, and stick with it!

I agree that consistency is important. But consistently helpful is a far improvement over consistently something-less-than-that. Consistently unnecessary is extra work and frustration. Consistently interfering impairs your ability and speed. Consistently mediocre is not the greatest.

No, overall, I think it is best to take a minimalistic approach. Yes, this is just my opinion. I am just one person, and some people have different approaches. But I have had experience in several shops and I have designed databases myself. I have a side database project outside my regular work where I am developing the entire thing myself, front end and back end, the the whole works. I'm sharing with you the benefit of my experience. I hope that these make sense to you, but whatever you do pick, you should have clear and compelling reasons for what you do. Notice the compelling part. Your default should be to avoid clutter and only add things if there is a materially demonstrable reason to violate that rule.

Now, first of all, much of my advice is meaningless if you're using a GUI where one performs the less taxing task of recognition instead of the recall required to write queries in text. If you are writing queries in text, more power to you, and if you keep doing it I predict you'll eventually see the sense of my recommendations.

But if you're using a GUI to write queries you'll only reach a certain level of ability, never stepping into the next level of professional skill and seasoned experience in the SQL realm. I haven't used a GUI to write a serious query in a very long time (I might have started a couple in an Access database but even then switched to SQL view). I recently helped someone who posted his query that was obviously built in a GUI and it had about 10 left joins. I seriously doubt they all needed to be left joins, which means he was killing performance for no reason.

So if you are writing queries in a text editor, here are some thoughts for you:

• Lose the underscores. They clutter up the screen. They take the shift key to type and also your pinky finger which has to move two rows up from the home row. And using CamelCase is just as clear and saves space and that pinky traversing. And if you're really feeling lazy you don't even have to hit the shift key.

order_id
customer_name
device_code_reason

OrderID
CustomerName
DeviceCodeReason

Look at it this way: if you can train your eyes to scan either kind of naming convention just as efficiently, why not use the one that involves less typing and takes less space on the screen?

• The prefix/suffix naming convention is not always understood or followed. In one shop I worked in they named tables with prefixes: tbl for base tables, tlk for lookup tables, and tin for "intersect" tables. But no one really knew what they were for sure. "tin" tables were supposed to be transactional tables that had constantly changing data such as orders and transactions. "tbl" tables were supposed to be things like customers and products. But the problem was that many tables didn't fit a clear definition, and people were careless, or came along years after the creator of the database (who yes used a GUI database creation program), and they got named wrong. So now after all that silliness and extra typing, there was a "tin" table that was really a "tbl" table and a "tlk" table that was really a "tin" table after some changes, and ... so on and so forth. Just leave off that junk.

• You'll eventually become completely sick of typing the extra characters over and over again for NO GOOD REASON. And you'll have these prefixes so wrapped up in your code and database that ripping them out becomes impossible. All those extra letters will come to be so much useless froofroo to you. At the same shop as tbl/tin/tlk they really named columns keyOrderID and frnkeyOrderID. They added nine characters of typing to EVERY SINGLE JOIN I ever had to write. After a year of working there, the pain was no less. It took me about 2 weeks to learn the primary and foreign keys of that database. The other fifty weeks I wished the database designer had left well enough alone.

• Naming is something that is so much more important than most people seem to think. What does it matter what we call things, as long as we're "consistent," right? I disagree. I need to come up with some good examples, but my experience is that the naming in a database the single most important thing that structures how programmers relate to the data and that determines their final speed and facility working with it in the long run.

Mislabeling things is like putting up signs at the cliff edge that say "keep going" and "this is the right way" when it's not. Label things appropriately.

If that sounds like a lot of bugaboo to you, then I can only offer that I think experience will teach the same lesson.

• Scanning and comprehension speed suffer when extra things are tacked on. When you look at a stored procedure or query, if you yourself weren't just working on it yesterday, then you have to digest it to understand it. The more prefix_blob_description_gorp you have in there, the harder it is to see what's going on. The assumption here is, you know your database like the back of your hand. Will that extra stuff speed you up now or slow you down? Who should you code for, the least common denominator or a reasonable level of facility? In fact, this brings up another important point:

• You do not want to give that new developer, who is unfamiliar with your database, a prematurely quick sense of competence and ability in your database. By doing this, you increase the chances that he or she will make some serious mistake. This is the opposite mistake of mislabeling. Now the path over the edge of the cliff is littered with so many friendly and distracting signals that the poor new person gets the signal to "keep going" when the correct response ought to be, "wait, I'm not sure I am doing the right thing." I'm all for helping new developers get going as fast as possible. But they ought to do this by studying the schema and practicing with it. Don't make it so apparently easy that even an ignorant person will believe he knows all about your database.

• The information is unnecessary and ultimately makes developers remember more information, not less. It sounds at first like a great way to expose information about the tables and objects. But the fact is that the period of time where a new developer isn't familiar with the database is actually very short. So for the benefit of two months of instant answers to the beginner, everyone else in the shop is suffering through unnecessary baggage.

• The implementation can change. What if you decide to change the structure of one of your tables, and to support your older versions of software, you build the new table with a new name and convert your old table to a view? Now you have tbl_Whatever that's a view. Whoops. And what if you need to use that really important sp, only you can't remember who wrote it? Now you have to look it up. What a time waster. What if you can't remember if it's an inline table function or a table-variable function? More wheels spinning.

If these seem farfetched, they are not. I have worked with databases that had "tbl" objects that were views and believe me it was a nightmare remembering which was which. Who cares what it is! Views and tables are used in exactly the same way so why call them something different?

• Don't label things with each developer's name. Why would you do that? Do all the developers write different versions of the same SPs? Are they not production SPs but simply a sort of learning environment where each can write whatever he wants so it's important to keep the names straight? In that case, why do the objects have the same owners? Just use a different owner name and don't put your name in the object. But wait, why are they even in the same database? Why does it possibly matter who wrote something in the function of the database as a whole? Things should be named according to what they are or do, not who worked on it first or last.

The effort of naming is not a small one. In fact, I often use a thesaurus when designing a database. I have at times spent (cumulatively over the project) hours working out the correct naming of things. But when I was done, WOW how things flowed. I've worked with databases where the detail table wasn't, and the parent table wasn't, and the order table wasn't, and the Determination column wasn't (it was DeterminationMethod). Every single one of those is a roadblock to proper usage, even to the developer who's been working with that database for years.

The way I do it is to never add anything to table names, and to use carefully chosen words for my functions and SPs that make it clear what they are. For example, all my SPs are either SubjectVerb or VerbSubject, consistently throughout the database (still experimenting with which I like better). Functions have words that make it clear what they do. NumberToHex is a function. OrderNumberToOrderID is a function. ProductDetailCount is a function, ProductDetail is a table, and ProductCreate is a stored procedure. Develop your own pattern. I can go through my own code so fast when I've been careful like this. The meaning nearly leaps out at me.

If you must use a prefix or a suffix (I lean toward suffixes myself because I like things to group by subject instead of function) why not a single character? Tables can still do without any fluff at all. Then your other objects are still distinguishable:

OrderNumber_OrderIDF


• Think of it this way: you're trying to build a race car--a sleek, elegant, efficient thing that can go as fast as possible and be interfaced with well. What good is a racecar that can theoretically go 200mph if the steering wheel has sharp spikes all over it and the driver has to spend extra time avoiding them? He won't be spending as much time at top speed as he ought to be.

If I was offered a consulting job on a database that had every stored procedure and object using different names based on who had written them, I would refuse the work for fear of going absolutely nuts. If I was forced by my situation to take the work, I would curse every day the people who decided that putting their names on their stuff would somehow have any kind of long-term value and I would quit the moment I found something else. Long after Shannon and Victor and Felix are gone from the company, poor developers are toiling away trying to remember if that was a felix function or a victor view or perhaps it was a shannon function and a felix view! GAHHH!

>>Call the same column the same thing in each table. Don't call it ID in one table and BlahID in another. Call it BlahID everywhere.

Again, some might disagree... Using a prefix might be of help sometimes - when writing SQL you get rid of table names if you have odtID as order detail PK and prdID as product PK and you need both of them in a view/sp/function.

To standardize the usage of ID argument, there is no difficulty in naming it @ID in all procs/functions.

• You have certainly chosen a kind of standardization. But you've chosen a kind that makes all the columns in different tables the same name. For a shop that wants to differentiate each thing by putting the type of it in the name, now you're departing radically from that philosophy by calling all the IDs the same thing. From the beginner/new developer perspective, it seems to make things easier in some ways--no looking up column names when selecting, right? But it's not easier, it's harder. If you ever get the experience of doing it both ways in a large and busy production database (that is, ID vs. TableNameID), I suspect you'll eventually agree (unless you've become dogmatic about it by then and don't really care about the actual function or efficency any more).

I prefer to standardize things in this way: If there is an ID column, it shares the same name as the table, + ID. Now there's no confusion about what the name is. Now if you have a query with many joins and you need to use ID you're not getting confused.

• You will eventually put the wrong alias on some join. You may not even notice because the query parses fine and you save your SP and off you go, but whoops, you got an effective cartesian product because your join condition referenced the wrong ID. Or just the wrong resultset. What a confusing mess. Much better, when you accidentally put D.ProductID instead of P.ProductID, for the compiler to complain that the table aliased by D has no such column. D.ID though... that would have compiled just fine and given the wrong results farther down the path, maybe even after release. It's much better to catch errors early.

• Selecting is not where the problem is. Yes, it's easier to say SELECT ID FROM Table. Fewer characters. But the real problem is in JOINs. The problem is in old resultsets you saved in Excel that you don't know what table they came from because they so unhelpfully say ID. You don't know what some error message means that a user reported to you because all he remembered was the main column that the constraint violated and he says "there was a foreign key constraint in column ID!" Great. That's every table in your database.

I don't know how to make this point as strongly as it needs to be made. Perhaps some of the other experienced SQL people here can chip in their opinions, if they even make it in to read this thread.

Maybe I'll come up with a compelling example later. For now, all I can tell you is that naming the same thing differently in different places is, long-term, problematic.

 

[mrdenny]

E,
Excellent tip.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[chrissie1]

Slow down on the coffee ;-).

Don't always agree on everything but I can see your point. I like my prefixes they are like family.

Christiaan Baes
Belgium

"My old site" - Me

 

[ESquared]

Yeah okay I do get passionate but it's a passion for speed, efficiency, and maximizing my ability. I've never liked things that try to "hold my hand" to "help" me do it. More often than not they just interfere and prevent me from going the speed I want to go.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[chrissie1]

I'll tell you why I went back to prefixing the table (not my .Net code). I had this table called user in mysql (not a reserved word) which I called from a Java app via JDBC and I kept gettting a table t_User can not be found which was reasonable because there wasn't a table t_User. So I was confident that the bug was in my code somewhere. So after a while, a long while I changed the tablename to tblUser and guess what it worked, so I changed them all so they would match the interior. Then at work we do casework so we have cases which makes for a table called case or not since that is a reserverd word so what should I do? call it case+? And I have tables called FiberFluorescenceColorN21, FiberFluorescenceColorA, FiberFluorescenceColorH3 why? because I have FiberFluorescenceIntensityN21, ... and I can't cut of the FiberFluorescence part because we also have other spectroscoops that use these light sources so adding a tbl in front of it doesn't really bother me. But thats me and I really like your point And I really want to use it but the wall is getting sick of my head.

Now look what you did. You made me write a long post ;-).

Christiaan Baes
Belgium

"My old site" - Me

 

[ESquared]

I've even stopped using full Hungarian notation in my programs. I was already starting to go by function instead of data type, using iVarName for all integers of any size, and fVarName for any flags whether they were Boolean or integer or what.

But with things like Shift-F2 that can take you straight to the definition of a variable and then Ctrl-Shift-F2 to take you back to the last cursor position, I'm having less and less use for these types of notation. I haven't given up on notation entirely, but I do use it less...

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[ESquared]

I realized today that what I've been focusing on is actually usability. Naming is not the primary thing I'm concerned with but how naming affects the way that a developer interfaces with the database.

Think of designing a database as creating a programming language. You are creating a whole new vocabulary and syntax for dealing with one particular set of data. Your objects are tables, your properties are columns, your procedures are stored procedures, your functions are user-defined functions.

Thinking of it this way, now it's not enough to simply have the program work when it is done or have the basic capacity to do anything that needs doing. The utility of the structure and names you've chosen play a large part in the flexibility, speed, and style the developers are forced to use by the language conventions themselves.

I went looking today for some user interface design guidelines. I want to know that the front end I am building is usable, but I don't have as much experience with that as I do with the back end, thus I am looking for some good principles to get there faster. All else being equal and assuming a good underlying database structure, I think being passionate about good user interface design can be the difference between a mediocre application and a spectacular one.

In my search I found a good basic article, User Interface Design Tips, Techniques, and Principles. And what do you know, there in section 2, UI Design Principle #6: The reuse principle.

[ul]Your design should reuse internal and external components and behaviors, maintaining consistency with purpose rather than merely arbitrary consistency, thus reducing the need for users to rethink and remember.[/ul]

Consistency is key. It's in fact the number one tip given by that article. But that's not the whole story by a long shot. Arbitrary consistency is fundamentally lacking when it comes time for a developer to actually use your database and its "language." Do everyone a favor and build that language well!

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[chrissie1]

Problem is, rules change over time.

Let's think of VB. They used to recommend hungarian notation and everybody did. Now they don't recommend it anymore because of intellisense there is no more need to call an object txttextbox because intellisense will tell you that it is a textbox. So MS base their recommendation on the IDE. But they I and they would still recommend using the underscore to begin a local variable to avoid keyword problems.

And it's not only usability but also maintainability.

I think you can have endless discussion about these things and common sense just needs to get the upper hand.

Christiaan Baes
Belgium

"My old site" - Me

 

[ESquared]

Christiaan,

I think your comments support my point. The need for Hungarian notation was that it was hard to tell what various variables did. Now those needs are obviated by new IDE features. Now the purpose for variable prefixes is largely gone. Do we continue to maintain Hungarian notation because it is "consistent" or do we have the option to dispense with it and find a new, sleeker consistency?

Perhaps you are right that the discussion can be endless, but in my opinion, that is not necessarily a reason for the discussion to stop. It is precisely because I think common sense does not get the upper hand that to me it is worth continuing discussions like this.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[ca8msm]

Now those needs are obviated by new IDE features.

What happens if I don't use that IDE? For example, I develop ASP.NET applications and that can be with either Visual Studio or Notepad. If I'm working on a project in Visual Studio, and I see an object named "txtName", I'll assume it is a TextBox and hovering over this will confirm this for me. If I'm using Notepad, the only way I can tell is by looking at the name and using Hungarian notation I would be able to take an educated guess of what it is without having to look it up in the HTML. Now if this variable was called "Name", then in this instance I probably would have to have a search around to find whether this was related to a control or a variable and if it was a control, what type was it.

I know that's slightly off-topic in terms that I'm using ASP.NET as an example, but the same principles aply no matter which language we use. I prefer Hungarian notation because of two things:

1) It visually tells me the object type
2) I'm used to using it

#1 is a very valid argument for using it, especially in situations where the IDE can't help me out whereas #2 isn't so valid as I could easily get used to using another method. I think the main reason that I do use it though is #1 and for me, this outweighs any of these benefits that you brought up before:

The prefix/suffix naming convention is not always understood or followed.
We have documents outlining the prefixes that are to be used so our developers can always see what conventions to use and it just becomes a way of working. We don't go overboard though with these prefixes and they are only used to specify the object type, so there's no "_Fred" or anything like that!

1) You'll eventually become completely sick of typing the extra characters
Personally, I rarely even look at the keyboard and the extra characters just seem to be something my fingers type for me without me asking!

I can only offer that I think experience will teach the same lesson.

You see the thing here is that this is your experience in your environment. Whilst I'm not saying you are completely wrong I will say that you are not completely right. The reason for saying that is that I think it comes down mainly to the environment you are working in. There's just as many users in these forums who will have just as much experience in these matters (if not more) and their experience may have taught them that using a method different to yours is actually better than the suggestions that you've come up with. This doesn't mean that either of you are wrong, it just means that each individual has found the best method for their environment.

To sum up where I stand, I think that you have some very good points but you can't just think that they will apply to everybody and that your way is the right way. Yes, you can give guidelines, but in some cases other peoples methods may be better for them in their situation.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[ESquared]

the same principles aply no matter which language we use. I prefer Hungarian notation because of two things:

1) It visually tells me the object type
2) I'm used to using it

I did not mean to make a strong case for dropping Hungarian notation in programming. It has its place and I use a slight modification of its traditional form (tracking function and being less precise on data type), and it is useful. At the same time, there are other cues besides prefixes that can tell us what something is. For example, if I use variables named Pos or Count you can pretty easily know they are numbers. Rs is ... drumroll please ... a recordset. In Access I do use three-letter prefixes for all form elements. I even wrote code that looks at every control on every form in my application and tells me those that don't meet my naming conventions based on their object type. So don't think that I have thrown out naming entirely. This naming is actually important in forms because if controls have the same name as fields in the recordset there can be resolution conflicts and you can get the wrong object.

I also at times have been a "notepad purist" for some of my work. For a long time I did a fair amount of HTML work in notepad, shunning graphical tools. Finally I started using an IDE (but I still don't use any GUI parts of the application). I find that it helps me do my job better and faster. I still have the ability to use notepad--my "purist" insistence on it for a long time ensured that--but I realized I care about function over form. I will use any and every tool available to me to maximize my speed, efficiency, accuracy, comprehension, and the like.

I am also not so convinced of the utility of being visually told the object type as part of the name. I believe rather in choosing more descriptive and appropriate names that help convey this information in other ways. And to me, the effort of finding out the type if I need to know it is less than the cost of the extra clutter by tacking on three extra letters to every variable. Perhaps it may sound like I have problems reading, but I don't think that's it. I really just like my information to be as dense as possible.

Put it this way: I only need to know the variable type once. But that information is embedded in every reference to the variable. It's extraneous.

I also use contextual information to structure the understanding of code. In a very small function, I just don't see the need to give types to everything. I find plain English words like StartDate easier to type and mentally parse than dteStart (or the grotesque dteStartDate). It's more English-like. I read English pretty well so I like to leverage that. When I am reading a book out loud, I turn the page about 7 - 8 words ahead of the word I am saying. Many people turn the page about 1-2 words ahead. I like to go faster and use that buffer space in my brain. Excluding unnecessary information is helpful in that aim. I want to be able to scan something very quickly, and in my experience dropping prefixes increases my speed.

Forms in Access are different because every control on a form (excluding subforms) is in the same big pile. It's like they're all global variables. If my VB code used all global variables, I can see why I'd need to use lots of prefixes to help me sort them all out. But in my functions and procedures, I don't need a lot of that stuff.

But more importantly than in programming I don't believe that the principles of Hungarian notation apply well to a database. In a database, we are working with a fairly static set of "variables"—your tables and columns are not (I sincerely hope) changing from day to day. There is consistent use throughout the application. When you open a new SP you've never seen before, and it refers to some Orders object, if you know your database at all you don't need to be told it is a table there, or every time you see the name perhaps dozens of times in the SP! If you really don't know what an object is, you're better off to go check it out and learn more about it than just its object type before you start using it! sp_help "objectname" is quite wonderful for getting fast information about things, almost all you might want to know. And sp_helptext "objectname" will take you even farther.

Another thing is what I said earlier, that when creating a database you aren't just "programming" but you are nearly writing a programming language, at the very least you are creating objects, properties, and methods. In VB they don't have "Dim objMyBox As [red]obj[/red]TextBox" nor do they use "[red]obj[/red]Me.[red]bol[/red]Visible = True" or "objMyBox.[red]int[/red]SelStart" or "objMyControl.[red]proc[/red]Move" Why is that? It's because once you are familiar with the object model and all the methods and properties, all that extra junk is just that: junk.

In creating a database you are creating a short list of vocabulary that has to be used over and over again. Don't make it longer or more complicated than it has to be. Go drop a listbox on a form and look at all the properties in the property explorer. What a drag if they all had three-letter prefixes on them that you had to use each and every time. Perhaps it would be helpful to the person new to the programming language... for about three minutes for each property. Then once familiarity with it sets in, it's worse than useless: it's in the way.

Also, given the limited set of variable types in SQL, I also find less case for Hungarian notation in the variables in my database's functions and stored procedures as well. @SQL is a char/varchar/nchar/nvarchar string. I hardly care which. If I need to know, I'll look it up once and then know while I'm working with it. I might see that @SQL variable 20 times in the SP. I don't want it to be @nvcSQL in every place, repeating what I already know and don't care to be battered with again, or having to repeatedly type it. I just want to use the dang thing, not name it every time.

I, Erik, get a picture of being unable to just say "I" in a sentence and having to say "I, Erik" or "I, human" each and every time. If you want to know who "I" is, you look at the top of this post once to see "ESquared" or at the bottom to see if I, Erik, have signed it, which I, Erik, have with my, Erik's, first name. You don't need me, Erik, to remind you each time I, Erik, say "I." Then you work with that background knowledge of who is speaking and can read my sentences faster (and I can type them faster) without the extra naming stuff.

Personally, I rarely even look at the keyboard and the extra characters just seem to be something my fingers type for me without me asking!

I too am a touch typist. I type standard English about 90 wpm (if copying other text or know exactly what I want to say). On a very good focused day I have clocked at 110 wpm but that takes extra effort I don't usually expend. If I type a word slowly or incorrectly I go back and repeat it about five times until I'm doing better. I'm always looking for extra speed.

So if I can type so quickly, why do I care about typing extra letters? The answer is twofold. 1: fewer letters means even faster typing. 2: I dislike the extra effort required to remember what it is I have to type! I carry in my mind the data type as some kind of concept or even "feeling" (a bad description I know but describing how one's brain works is always difficult). It is distinct from the name. txtBlah is to me really Blah and a little extra hint of flavor. So when it comes time to use Blah, there an extra check: oh... what flavor is it and how do I code for that oh yeah it's txt.

You see the thing here is that this is your experience in your environment. Whilst I'm not saying you are completely wrong I will say that you are not completely right. The reason for saying that is that I think it comes down mainly to the environment you are working in. There's just as many users in these forums who will have just as much experience in these matters (if not more) and their experience may have taught them that using a method different to yours is actually better than the suggestions that you've come up with. This doesn't mean that either of you are wrong, it just means that each individual has found the best method for their environment.

You are 100% right—to each his own.

As for the environment, if the shop you are working in does things a certain way, then you likely have no choice but to follow suit. But some day, you'll be part of a new project where you have a chance to try out some of the things I'm saying and influence what standards are chosen. Why not try it?

If you mean something else by environment than simply "the way things are done here" or "the way the lead developer requires" or "the way the database already is," would you explain that?

I am not so convinced though that many people have actually tried both ways and found the prefixes to be indispensible. Have people done any at least medium-sized serious production project where all the database objects and column names did not use prefixes? Have they worked extensively with SPs that did not use Hungarian notation in their variable names and that were also named well to avoid confusion? Experience with the prefixes working "well enough" doesn't prove their superiority over another method if that other method has never really been tried.

In closing let me offer again what I said near the top of the original post:

Yes, this is just my opinion. I am just one person, and some people have different approaches.

I hope that these make sense to you, but whatever you do pick, you should have clear and compelling reasons for what you do. Notice the compelling part. Your default should be to avoid clutter and only add things if there is a materially demonstrable reason to violate that rule.

My ultimate hope is to help people become more efficient, powerful developers.

Erik

P.S. I have a thought. Do you name all your columns with their data type? If not, why? Are there any reasons for prefixing tables "tbl" that don't apply equally to columns? And if columns don't get prefixes, how does one deal with the problem of not being visually told the data type?

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[ca8msm]

If you mean something else by environment than simply "the way things are done here" or "the way the lead developer requires" or "the way the database already is," would you explain that?

No, that's pretty much what I meant although I was thinking more in terms of how the "team" works. There may be 7 or 8 developers who at some point write or modify the database and if the rest of the team works in the same manner it makes it easier all round.

P.S. I have a thought. Do you name all your columns with their data type? If not, why? Are there any reasons for prefixing tables "tbl" that don't apply equally to columns? And if columns don't get prefixes, how does one deal with the problem of not being visually told the data type?

No, I don't prefix all of my columns with their type. I may for example (like you did above) have a field named StartDate so that visually tells me the type in this instance. When I referred to Hungarian notation, that was probably incorrect of me as I'm referring to something that identifies the type, not necessarily a prefix/suffix. For example, I might look at an object named "tbl_Users" and know that it's a table. I don't really need to know any more that than so it's not taken any further than that (i.e. I don't care if "Fred" created it so his name isn't going into the table name).

As for why I don't prefix my column names with "int" or "vch", it's because I know it's a column and I prefer to name them with something more appropriate that suggests the type (e.g. "Comments" will indicate to me that is stores some text - what type, I'm not too bothered about for now). So, with a column I know it's part of a table so I can make an educated guess as to it's type. But, if I see an object named "Users" I may not know what it is and therefore might have to guess if it's a table, a view or an SP. I'm not sure if this makes sense or not, but it does to me!


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[r937]

erik, do you have a web site?

r937.com | rudy.ca

 

[ESquared]

As for why I don't prefix my column names with "int" or "vch", it's because I know it's a column and I prefer to name them with something more appropriate that suggests the type (e.g. "Comments" will indicate to me that is stores some text - what type, I'm not too bothered about for now). So, with a column I know it's part of a table so I can make an educated guess as to it's type. But, if I see an object named "Users" I may not know what it is and therefore might have to guess if it's a table, a view or an SP. I'm not sure if this makes sense or not, but it does to me!

I would never name an SP without a verb. Users clearly can't be an SP. And if it for some reason is super important to know whether Users is a view or not, how about something more minimalistic like just V? And how about putting it on the end instead of the beginning so objects are sorted by subject instead of type: UsersV instead of VUsers, so the first part of the word gives you the important information instead of the end of it.

Even if Users was a function, every time I will see that function name I'll know that it is a function because it will either be 1: in the functions folder or 2: have parentheses after it and be used as a function in code in a way that tells me if it returns a table or a scalar. No questions there, either.

I still haven't heard any really compelling (to me) reason to name tables with a prefix, any reason that wouldn't equally apply to a column. Sure, you know it's a column, but you don't know what data type. Sure, you know its an object in your database, but you don't know whether it's a view or a table. Does it matter? You can learn any of these quickly, especially by naming things well as you just said. I will echo your own words back in bold here:

I don't put "tbl_" prefixes on my tables because "I prefer to name them with something more appropriate that suggests the type". Or perhaps I should say, I prefer to name everything besides tables with something more appropriate that suggests the object type. Using this scheme, I have great information about what things are.

If anyone don't agree and thinks I'm way off base then they can rest secure in their own professional opinion and do it their own ways with no problem! But if my ideas keep nagging at people over time, perhaps actually trying it out my suggested way is in order to see if those "tbl_" prefixes are really necessary after all.

If the environment you're in requires these prefixes, then use them! No objection there.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[ca8msm]

Using this scheme, I have great information about what things are.

Me too, apart from I think I have even more information at hand by visually looking at the object. Typing a prefix (or rather something that identifies what the object is) as well as giving it a descriptive name doesn't interfere with the speed that I work, doesn't look like junk or clutter to my eyes, helps me identify things quicker and speeds up my/our development. That's why regardless of the experience you've had, I'll still use the method that suits me and my environment.

perhaps actually trying it out my suggested way is in order to see if those "tbl_" prefixes are really necessary after all.

You shouldn't assume that people haven't tried other methods. There's lots of situations where databases have been developed differently to how you or I may work, but after experiencing these methods we all come to our own conclusion about what is/are the best methods. Just because I don't share the same conclusion as others doesn't mean either of us are wrong, but I'd prefer to guide them and let them come to their own conclusion on their own rather than try to tell them that "this is the right way". Personally, I've tried most (if not all) of the methods you've suggested and I choose to use some of them and also not to use others.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[ESquared]

Me too, apart from I think I have even more information at hand by visually looking at the object. Typing a prefix (or rather something that identifies what the object is) as well as giving it a descriptive name doesn't interfere with the speed that I work, doesn't look like junk or clutter to my eyes, helps me identify things quicker and speeds up my/our development. That's why regardless of the experience you've had, I'll still use the method that suits me and my environment.

Then you are doing exactly what you already know is best for you. You have my full support for doing things the way you believe is best.

rather than try to tell them that "this is the right way". Personally, I've tried most (if not all) of the methods you've suggested and I choose to use some of them and also not to use others.

You're right that I made some assumptions. Some of those are obviously wrong. I'm sorry about that. Your experience is as valid as mine.

My experience[sup]*[/sup] has been, though, that many shops and people that do use the practices I deprecated here have never tried anything else or thought that anything else was even possible. I have frequently seen slavish adherence to supposedly good rules again and again, rules that aren't rules at all. Thus my attempts to show some different ways. (Ways that, yes, I think are better, even if that can't be proved objectively). Your deliberate choosing raises you above the mass and is evidence of a keen mind. You think your ways are better. Great!
[sub]* - That is, it is not necessarily true or representing objective reality[/sub]

-------------------------------------------

You guys, this stuff is just my opinion. I get the impression that there are some defensive feelings going around. There's no need for any of that. I'm not criticizing anyone. If you disagree with my thoughts based on your own professional experience, more power to you! If your way of doing things is clearly justified in your own mind and you believe you have compelling benefits from it, then I've achieved at least half or more of what I set out to do!

It really confuses me when people keep telling me "but this is just your opinion" when that's what I said from the very beginning. Why the need to keep saying that? Everyone else's views are "just their own opinions" as well. Great, now we're even!

I'm the kind who likes to go deeper, to take things as far as I can take them. I study shortcut keys so I can go faster in Windows and in all my applications, even printing the list of shortcut keys for a program so I can study it and learn the functions. In Excel I do very little mousing at all even with very complex spreadsheet manipulations. I analyze and re-analyze various methods of performing a query until I've found the most efficient or learned something from it. Perhaps one could even say I am obsessed with speed and efficiency, but in my job I consider that a positive. Maybe it's a little more than most people want--so take what I say with a grain or seven of salt.

For example, in Excel I often take a selected list of items and copy only the unique members to another column all from the keyboard, from memory: Alt-D, F, A, Alt-O, R, T, {destination reference}, Enter. In 5 seconds without moving my hands from the keyboard I have a list of the unique items in a column (must have a header label to do this). Is it objectively better? Maybe not. Is it faster for everyone else? Maybe not. It is faster for me? Yes! Are there people who might like to use and benefit from techniques like this? Probably. I complain greatly every time I go to do a search and replace in Excel 2003 because they changed the behavior so the default active control is the Replace box instead of the Find box. Now I have to hit Shift-Tab first or I have to enter my search/replace items in reverse order. Why'd they do that? Don't "hold my hand." The first thing I do on a new computer is turn off personalized menus everywhere, then disable windows in taskbar everywhere. More "hand-holding" that just slows me down. Perhaps now you can see why "tbl_" and other prefixes to me are more similar unnecessary stuff that I don't want in the way of doing the real work.

I truly hope that we can all agree to disagree without any rancor. I think no less of anyone who uses extra stuff in their object names. And I hope you think of me as simply passionate about development and programming and usage. You've seen my queries or even complete functions and SPs. Did they have any merit? Were they well-constructed or thought out? Were you confused about what things were or thrown off by my naming conventions? Am I just a weirdo and no more?

You know, I don't trot out my opinions on naming conventions very often. I have rarely, if ever, criticized the regulars' naming of objects. And Mike (LNBruno), I apologize now for ever saying anything about your style of comma placement, even obliquely. I had no intent to offend. (I'm guessing that I offended because of your "Betty" post above--or is it there now anymore). And your post just now again. I'm not sure why you are addressing everyone else to tell them that you don't take me seriously. I would expect you to address me directly. But either way I fully support your right to think I am a crackpot and ignore anything I have to say.

Have you found any value in my SQL posts? Has anyone learned anything from me? I've learned from all of you, truly. I know for a fact that by seeing everyone else's solutions to questions asked in this forum I have learned more than I ever would have on my own. Thank you all for that. The participants in this thread so far I can say have individually each taught me something. And you all have also earned my respect because of your general ability. And I'm sure that you all still have more to teach me.

I did recently offer an opinion on naming to someone who was clearly a beginner and was not only adding type prefixes to objects but also developer name prefixes. That thread was actually the inspiration for this thread. Given the (to me) surprising resistance I got there and here, I'll work hard to be more politic about naming in the future, if I say anything at all.

You all are also free to disagree with me and I hope that I am free to disagree with you. If you argue my points, I hope that I am free to argue yours.

Another thing that comes to mind is that recently a friend of mine expressed how I come across sometimes as pretty rough--where we are not just expressing our points of view but the conversation becomes framed as a competition, a fight, a challenge. Maybe it's a reasonable guess that that's what's been going on in this thread--my direct and no-holds-barred style having been less effective than it could be. All I can say is that it's something I'm working on and I truly have only good intentions here (I know, I know, a certain road is paved with them). And, yes, I have a passion about naming, about deliberate restructuring and use of our ideas about ideas, about an examination not just of knowledge but about what we think we know about knowledge and how we use it, about speed and efficiency, about all that. I've been told dozens of times in my life that I am an intense person. That type of trait is unfortunately interpreted at times as hostile and I can see why.

So, all that drivel said, I sincerely hope that this thread provides value to people and that the informational content can be focused on instead of the perhaps too-strong style.

--------------------

Rudy, I don't have a web site. If (randomly guessing) you might want to contact me, "moc tod liamtoh ta egamlaprov" might help you do that. If not, forget I said anything.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[r937]

i was asking if you have a web site because clearly, you have some good things to say, and a web site is usually where people put stuff like this



r937.com | rudy.ca

 

[ESquared]

I just reread this thread and even with some hours time to reflect, and an open mind, I don't find my posts to be all that strong or outrageous.

I didn't say the following quote in isolation:

perhaps actually trying it out my suggested way is in order to see if those "tbl_" prefixes are really necessary after all.

That quoted by itself makes it look like I'm arrogantly assuming no one has tried it. What I actually said was:

If anyone do[es]n't agree and thinks I'm way off base then they can rest secure in their own professional opinion and do it their own ways with no problem! But if my ideas keep nagging at people over time, perhaps actually trying it out my suggested way is in order to see if those "tbl_" prefixes are really necessary after all.

If you've tried my ideas and they don't work for you for some reason, then they won't be nagging at you over time. And if you think I'm way off base then as I said, no problem. Implicit in my final sentence was the idea that if in fact you are not so sure—as might be suggested if my ideas keep bothering you—then perhaps it's because you haven't really tried any other way besides tblThis and vwThat and proc_TheOther and fn_s_SomethingElse. If the shoe fits, wear it. If not, it's not something to really concern yourself with.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[LNBruno]

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[Ach005ki]

Hi guys - I don't post much here but I try to read up on many threads (even ones which don't directly affect me and those topics on which I haven't yet stumbled in the course of my work).

I very much appreciate all the input in this thread, but especially from Esquared who has started the ball rolling on this - I am nothing but a beginner in terms of SQL and I currently have to perform my queries through MS Query. It can be difficult to determine what is a table and what is a view (to start with, and without using the GUI to build queries... using which in turn has its own problems for a budding SQL pupil...), but I take in all of what has been written as I hope to some day have a different tool with which I can cut my cloth in a more efficient fashion... and that "more efficient fashion" would encompass the techniques, tips and tricks I'm gratefully receiving from the kind, sharing folks here!

Mark, somewhere near Blackburn Lancs!