July 24, 2007 at 3:47 pm
NOOO!!!! <sob!>
In 99.9% of the places Hungarian notation is used, it is used incorrectly. It was intended to signify the type of data a variable contained, not the datatype of the variable. And there is no reason whatsoever to extend this to tables, views, stored procedures, UDFs or any other database object. Is there any context where you come across the name of a table that you don't know it is a table? What does the "T_" prefix tell you?
And why all caps? Sure, if you're working with Oracle which converts names to caps anyway -- but for SQL Server, et al, which maintains the case of the name, why not use OrderItem, OrderItemID and so forth?
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
July 25, 2007 at 1:23 am
Method 2 is my method of choice.
As for the example above, in that situation I'd probably name the columns FromStageID and ToStageID to keep the 'StageID' part.
September 25, 2007 at 10:18 pm
Hands down method 1! If you've ever done any large database designs, you'll thank yourself for doing it as the millions of Procs and Views you write won't cause too much carpel-tunnel. I always make the Primary Keys just "ID". I used to use method 2, however, it's just too much work to do that anymore. Plus, when doing JOINs they look more object-oriented (From Orders Inner Join OrderDetails D ON (O.ID = D.OrderID)).
Sorry, but I'll never go back to Method 2...
October 2, 2007 at 4:02 am
FWIW
I prefer to use pkTableNameID for (you guessed it) the primary key for that table. Tables being pluralised, so tblCategories would have pkCategoryID.
Then fkTableNameID clearly describes the fact that it is a foreign key and not the primary key, but while letting me know that it is an ID.
You don't then need to know my schema to know which table the pk belongs to, its almost self documenting too so it makes it much easier for someone else to pickup.
I don't see that saving a few key presses is worth sacrificing the legibility of your columns, it seems to be straying close to the idea of having columns named c1, c2, c3 etc... and that's a real headache to pick up 6 months later.
July 15, 2008 at 7:11 am
Disadvantages of method 1-
1. As per the best practices it is recommended that every column name should be self explainatory or meaningful. Which gives more comfort to developers and understanding the design is simple.
2. You should aviod common names and system identifiers like ID, DATE, NAME etc as a column names to avoid the confusion acorss data model.
3. Its wrong that it requires less typing. You alias typing efforts are increased. You can have ID as a PK column name only for parent table and not for child tables (Because they have another ID column as their PK) so typing efforts are not reduced.
4. It is importand to keep consistancy of column names across table wherever possible for better understaning of design, readability and to avoid mistakes while writing queries and to increase the efficiency.
5. If you are joining two tables which are not having PK-FK relationships and if ID is the PK in both tables its mandatory to use alias's in joins, group by and order by etc. In case of second method you can just go ahead with column names because those are unique.
6. Method two is used almost in 100% business scenarios. Method one is used during study phase. (Which is also a bad practice). Even sample databases given by microsoft uses method 2. Articles published on reputed sites like http://www.agiledata.org uses method 2.
Guys can you add any other points to this?
July 15, 2008 at 7:12 am
Disadvantages of method 1-
1. As per the best practices it is recommended that every column name should be self explainatory or meaningful. Which gives more comfort to developers and understanding the design is simple.
2. You should aviod common names and system identifiers like ID, DATE, NAME etc as a column names to avoid the confusion acorss data model.
3. Its wrong that it requires less typing. You alias typing efforts are increased. You can have ID as a PK column name only for parent table and not for child tables (Because they have another ID column as their PK) so typing efforts are not reduced.
4. It is importand to keep consistancy of column names across table wherever possible for better understaning of design, readability and to avoid mistakes while writing queries and to increase the efficiency.
5. If you are joining two tables which are not having PK-FK relationships and if ID is the PK in both tables its mandatory to use alias's in joins, group by and order by etc. In case of second method you can just go ahead with column names because those are unique.
6. Method two is used almost in 100% business scenarios. Method one is used during study phase. (Which is also a bad practice). Even sample databases given by microsoft uses method 2. Articles published on reputed sites like http://www.agiledata.org uses method 2.
Guys can you add any other points to this?
November 20, 2008 at 8:02 pm
I'd say method 1 (using plain ID for PK) satisfies points 1,2, and 3 perfectly.
And that is why I like it.
But I would vote for method 2 on yet another practical advantage it provides:
imagine you need to find all the places where your Order.ID is used in some large script or program code. Searching for "ID" is meaningless (1000s of them), "Order" is also too frequent, and you end up missing code like this
SELECT O.ID, ... FROM Order As O ...
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply