February 28, 2007 at 7:33 am
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mlato/2895.asp
Regards,
Michael Lato
March 6, 2007 at 11:10 pm
To the point. A doc that DBA's and people in the design phase should commit to memory from day 1. Note .... going down the wrong road and trying to get back to this point is not fun though!
March 7, 2007 at 1:31 am
I don't agree with the naming conventions of identifying columns (<tablename>ID). That does not work with compound identifiers. First, you need more than the tablename, second, "ID" suggests that this column is an unique identifier.
I prefer using a PK (Primary Key) suffix or prefix.
March 7, 2007 at 2:26 am
Hmmm... so which one is the foreign key and which one is the primary key? Or more importantly which table is the foreign key referencing?
I tend to use pk_<tableName> and fk_<thisTableName>_<referrencingTableName>. If you wanted to know the columns being referrenced then the information is within the key.
What would happen with a composite unique key made up of three columns? I'd just do this:
uk_<tablename>
March 7, 2007 at 4:55 am
I agree with most of what is presented in this article. You can go over the top with naming conventions but this is about right IMHO.
The compound key constraint naming is the exception to the rule as noted but that shouldn't stop you following this convention or a slight variation of this convention.
Generally naming a fk constraint <table name>_<constrained col name> will work OK if your pk table uses <table name>ID as its primary key column - it's then obvious which is the pk and which the fk. I prefer qualifying constraints with _FK/_PK/_DF suffixes rather than prefixes. This means you can list sysobjects by name and objects belonging to the same parent are listed together.
I opt for no prefix for tables (and just avoid reserved key words) a vw- prefix for views and use v- to distinguish validation tables from data tables, also using a z- prefix for tables that contain system data rather than user data. E.g. Customer, vSalutation, zVersion.
March 7, 2007 at 5:01 am
I stopped reading as soon as I saw the recommendation to use Hungarian notation ("t" prefix for tables, etc.) I wish that foolishness had never been started!
March 7, 2007 at 6:28 am
I advocate the use of English for naming standards. Never use abbreviations or acronyms. If the word can't be found on dictionary.com then don't use it. No prefixes or suffixes are necessary, just apply the rules of English that we all learn early in life.
A table that contains information about employees becomes the Employees table, a noun. A table is a collection similar entities, plural.
The stored procedure that inserts an employee to the Employees table is named AddEmployee.
Stored procedures perform actions considered verbs.
Wen the enterprise vocabulary becomes English the maintenance cost goes down. You write a program or system once and maintain it forever.
March 7, 2007 at 7:41 am
While I agree with the essence of the previous posts, saying something is "nonsense" without constructively suggesting alternatives or even saying why is not helpful.
As for a statement like "never use abbreviations or acronyms", well ne dis jamais jamias (never say never). As long as they are used sparingly and consistently I don't believe there will be problems.
I was dismayed to see the word "Data" included in one of the database name samples. Terms like "Data" and "Information" should rarely if ever appear, as they add no descriptive value. Was the db for an OLTP/TPS or a Mart? Well then say so in the name.
Your backup files suggestion is fair, and I have seen variations commonly used. Although one could argue where environments keep the date and time as displayable/sortable file metadata, why bother with putting it in the file name?
In my experience, prefixing tables with a "t" (and views with anything) is unnecessary and counter productive. For the most part, when doing development or maintenance, the person on the job always has the context of the object readily available, if not in the forefront of thoughts. In any event SQL Server will easily tell you whether an object is a view or table or whatever. Even when writing queries an objects "tableness" or "viewness" is not relevant. Tables will have simple, short names when named correctly. I believe they should be named in the plural (e.g., Users), although I keep entity-type names in data models singular. View names tend to longer than table names because they should also describe the view's purpose with an allusion to whats in the where clause and/or whether its a UNION or OUTER JOIN and so on.
In column names I think classwords add value. I think "ID" should be used anywhere it makes sense (e.g., UserID), but always used as the classword for surrogate keys. PhoneNumber is a much better name than Phone, as the Number classword means fixedlength string primarily comprised of digits. In my opinion, using prepositions like on and by should be avoided; classwords tend to make the column name clearer, as in CreateDate, CreateUserID.
I also think use of prefixes on columns indicating "indexness" or "keyness" is pointless and serves to obfuscate.
I could go on with more suggestions, but I've spent enough time on this already. I hope my suggestions will give the author and other interested parties something to consider.
Regards all - Todd
March 7, 2007 at 7:59 am
Lyle wrote: "A table that contains information about employees becomes the Employees table, a noun. A table is a collection similar entities, plural. "
I strongly vote for singular, because (OO) classes and tables are both but entities, and no java programmer would ever name a class plural. Accordingly a table should be named as an instance of the entity i. e. singular.
March 7, 2007 at 8:06 am
the article intended to cover naming conventions but i found its names quite uncommonly named. name employee table 'tEmployee'? give me a break.
steve yang
March 7, 2007 at 8:28 am
I appreciate the fact that universities have been teaching the singular concept for years. It is a topic that I have debated with folks like Ted Codd and Chris Date for decades. I also love the controversy bred by this topic.
A roomful of human entities is not a gathering of person, but people. A company meeting is not attended by employee, but employees. Reinventing the language "wheel" is an unnecessary expense that leads to higher maintenance costs, longer development times, and overall confusion.
One of my first mentors was Peter Page', the inventor of NATURAL language. His intent was that the language of programming read like a great book. Have you read any great books lately?
March 7, 2007 at 8:36 am
I've gone back back and forth on the singular v. plural table names argument. In the early days when I was a systems analyst, I was taught that table names should be singular because entity type names where named singularly. Well I changed my mind in the last 2 or 3 years because a table is collection of instances, and it makes more sense to refer to a collection in the plural.
As for the "we don't do it that way with OO" argument, I must admit surprise that someone would try project an object oriented programming standard onto a relational database object.
For good discussion on general SQL naming conventions, including plurals see: http://www.dbazine.com/db2/db2-disarticles/gulutzan5
Regard all - Todd
March 7, 2007 at 8:39 am
What a can of worms. For what it's worth here's another opinion. Remember it's worth what you paid for it:
Prefixes - as a general rule I avoid them like the plague unless they server a very specific and limited purpose.
Tables - no to any prefix unless it's an enterprise wide database and then the prefix should be a "department" name or mnemoninc (hr, sls, mkt, eng, prd). Prefixing a table with t is the same idea the author discourages when refering to data types in column names. After all isn't a table the data type for a table? Tables should be singular nouns. If a table of Employee records is plural (Employees) then then the follow on logic is that the wrapper CRUD procedures would be CreateEmployees, ReadEmployees, UpdateEmployees and DeleteEmployees. What if I only want one? The name may make me wonder if it only creates multiples.
Views - one of the two places where I really like adding a type identifier (suffix) of _v or _vw. Views are special case virtual tables and updateability rules will vary. If I'm messing with a view in procedural code I want something that smacks me on the head to let me know I'm working with one.
Procedures - See rule number. In a moderately complex database you would end up with all Employee related procedures scattered all over the browser window. Change to a suffix and magic happens because you then end up with Employee_get, Employee_ins, Employee_upd and Employee_upd. Not really rocket science to find or figur out what those do.
Functions - the other place I like the type identifier and this time it's (gasp) a prefix (ufn_). This is to let me know it's a user function. Again this one smacks me on the head and lets me know I'm working with a user functoin and not a built in one. ufn_Split(), ufn_LastDayOfMonth(), ufn_ZeroPad().
Relationships - I head to the OO world here with ChildTable_has_ParentTable. It really helps out.
Indexs/Keys - Primary: PK_TableName or TableName_PK; Unique Key: UK or AK and TableName -- If I've got more than one I reevaluate my database design. Foreign Key (index) FK and the ReferencedTableName. Wheter you use a prefix or suffix is not relevant to me as I don't "code" against them -- just be consistent.
One place where the author and I do agree is on the need for being consistent. I hate to alias anything that's used by another program (columns and parameters) so just don't go there.
And remember; opinions are like aXXholes, everyone has one and they're all full of it.
--Paul Hunter
March 7, 2007 at 9:07 am
I tend to favor using suffixes if you want to distinguish views from tables from procs, etc. The big reason is that I want all things affecting a particular area, i.e. Employees, to be grouped together when I'm looking at the objects. So table Employees is next to views, EmployeeSalary_v, EmployeesTerminated_v, etc.
Also uspEmployeeGetOne, uspEmployeeGetAll is easier for me to find in QA than uspGetEmployees, uspGetAllEmployees, which may be separated by many other objects in a list.
It always gets weird and hard when you start combining multiple objects into one function, but I think you need to do the best you can.
It's more important to be consistent than to worry about which standard. Within days, you'll pick up any standard and get used to it. But if things are haphazardly named, it's way harder than if every table has a t prefixed.
March 7, 2007 at 11:14 am
I think Steve hit the nail on the head with why people get into using prefixes. A lot of people got used to naming their database objects with prefixes precisely because they "group together" well in Enterprise Manager and other GUI tools, so they could easily group user-defined objects for easier management.
OTOH, with the addition of schema support in SQL 2005, I think the rationale/justification people use for built-in prefixes is gone. Schema support in SQL 2005 and how it affects naming conventions would be a nice addition to this article.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply