Worst Practices - Part 1 of a Very Long Series!

  • jodiem

    SSC Veteran

    Points: 254

    Andy, thanks for the start of what I'm sure will be a very interesting series.

    On Hungarian Notation, I agree that it's probably WP to use it on column names but if people want to change thier column names then there is a tool that will help - Speed Ferret http://www.speedferret.com. I like this tool because it does give you the ability to change your mind as the database grows and changes - there are a few restrictions to it however - it does not yet support SQL Server 2000 (I've used it a few times but with great care - ie back up the database first), and there's no way to change column names referenced in your ASP although it will work with VB. (for changing ASP I use windows Grep http://www.wingrep.com)

    on Naming Conventions generally I've always stuck by the rule that It doesn't really matter what the naming convention is, as long as it is documented and ahdered to. A good BP/WP article/discussion could be had on the use of the tbl prefix before each table name (vw for views etc). Some people are passionately against it and say it adds time to reading the code. I use it personally because our DB has a view on each table and I need to know whether it's the view or the table when I'm reading a SQL Statement. Any other views on this?

    Edited by - jodiem on 10/07/2001 8:09:31 PM

  • dood

    SSC Enthusiast

    Points: 116

    I graduate from university last year and what you are saying is what i was taught. I irony is that been in the work place for 1 year i have found most of the databases within the company i work for do not follow the way i was taught/you have noted. It's a pain the the butt as it takes ages to understand the database.

    Rock on, i wish more "IT/database proffesionals" would read and follow you suggestions, it would save agravation + time

  • isdfnmo

    SSC Journeyman

    Points: 77

    Good start to what i'm sure will be a very interesting series ! I am in the middle of creating a BP guide for my development team so I shall be keeping a close idea on the boards.

    My take on the last example of WP (hungarian notation for column names) is slightly different. The fact that it is considered a WP is surely more to do with the limitations of SQL Server than the actual practice being poor in that there is no global search and replace functionality with SQL Server.

    I guess that using hungarian notation in column names, where it is such a pain to change as the db becomes more sophisticated, really emphasises good analysis and design.

  • David.Poole

    SSC Guru

    Points: 75191

    I agree with using Hungarian notation for objects and variables.

    I would NOT use it for anything that a user might see such as a column name.

    I picked up the idea of using Hungarian Notation from Ivor Horton's C++ book and it seemed like a good idea. Although aimed at variables, pointers, properties and classes there was no reason to limit it to C++, VB etc.

    Are there any agreed standards for SQL objects or indeed objects in general?

    I use:-

    Tbl_ identifies a table.

    Idx_ identifies an normal index.

    Pk_ identifies a primary key.

    Unq_ identifies a unique key.

    Vw_ identifies a view.

    usp_ identifies a user (non system) stored procedure

    uxp_ identifies a use (non system) extended stored procedure.

    df_ identifies a default.

    rl_ identifies a rule.

    ty_ identifies a user defined type.

    If you are going to use some form of Hungarian Notation then it has to be adopted as a standard across the organisation.

  • Andy Warren

    SSC Guru

    Points: 119676

    Thanks for all the comments so far! I have not tried Speed Ferret, but I do have a freeware add-in for VB that does much the same thing. David, I do like Hungarian for objects I think - with the exception of table. I know that's not exactly consistent! One thing to consider is that the one place in VB where it's recommended that you NOT use this notation is in public objects. If you're providing users with a class contained in a dll, all the methods and properties should be 'English', not 'Geek'!

    Andy

  • cdeatherage

    Grasshopper

    Points: 21

    Andy, I too am really looking forward to this series. I have never seen hungarian notation used in column names. I do agree it would be a disaster! The practice I use with columns is to append part of the table name to the column (except foreign keys). The classic Employee table is a good example. The column names might be empID, empLastName, empFirstName, etc. When working on tables I always keep a copy of the data dictionary handy and that's how I determine the data type and domain of the field. It also helps (a BP of course) to have data standards. So e.g. if zip code is always a 9 character text field, any time I see zip code, I know the data type. Of course, this doesn't always work with legacy databases.

    Anyway, keep up the good work!

  • Andy Warren

    SSC Guru

    Points: 119676

    Something I see used VERY little is user defined type - talk about self documenting, look over to see that the column is of type zip code. Now whether that is a BP or WP is a subject for another day!

    Andy

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715912

    I'm with Andy on this one. I used to use Hungarian notation in coding, but never in column or object names. I think it is a disaster. Tool or no tool, the rebuild time for a table might be way too high for a name change.

    One other note, since I've started working with .NET, MS does not recommend using hungariang notation, at least for C#.

    Steve Jones

    steve@dkranch.net

  • Andy Warren

    SSC Guru

    Points: 119676

    Did they give a reason why (for not using Hungarian)?

  • jodiem

    SSC Veteran

    Points: 254

    quote:


    When working on tables I always keep a copy of the data dictionary handy and that's how I determine the data type and domain of the field.


    Is there some report or query that you use for this? What information does it contain?

    What do people think of having the whole database diagram up on the walls? Which is what we are trying to do at the moment - but it's 6 A0 size sheets of paper - very big. So if there is a more beneficial way to give this information to the coders, I would be very interested in others' comments.

    Jodie

  • Andy Warren

    SSC Guru

    Points: 119676

    I've got one of those big diagrams of the SQL system tables - useful at times, especially during a discussion with someone else where you both easily see it. I think a lot of it depends on how often you need the document. Sometimes you want to look at the big picture, sometimes you're mired in detail! I've found the object browser in QA to be a quick and easy way to look at table level info.

    Andy

  • Andy Warren

    SSC Guru

    Points: 119676

    Comments posted to this topic are about the item Worst Practices - Part 1 of a Very Long Series!

  • VegaMachine

    SSCrazy

    Points: 2020

    While I do agree that changing column names in a table is generally a bad thing to do, I have to say that with proper DB design upfront you should never have to change names, also if you do come across a change that has to be made you should still be in a developement environment and not far enough along to cause serious headaches. With the use of business objects you would reduce maintenance by haveing your DB integration code located all in one place.



    - Vega

  • Andy Warren

    SSC Guru

    Points: 119676

    I'll go along with the idea that if you've done a really good middle tier, you don't care as much about the column names. Or at least they are all in one place. I'm not sure that holds true - what about reporting, how many reports go through the business layer and not directly to the tables (Crytal Reports, etc)?

    One other point - if you're truly working with a middle tier component, then why even worry about Hungarian, since only the middle tier developers will see them? Is it worth the effort then?

    Im not complaining about your comments - I appreciate your contribution to the discussion!

    Andy

  • PBirch

    SSCrazy Eights

    Points: 8483

    Good article. I'm not a fan of Hungarian.

    Sometime down the line write an article about stored procedure naming conventions (my favorite gripe). I have developers who "automatically" name their stored procedures sp_ ("because Microsoft does").

    Patrick Birch

    Quand on parle du loup, on en voit la queue

Viewing 15 posts - 1 through 15 (of 73 total)

You must be logged in to reply to this topic. Login to reply