Naming Conventions
-
Hi
I was just wondering if the was a pdf somewhere on the interwebs that has naming conventions for MS SQL :-), I'm sort of winging it from what I see in examples but want to do it right so that it's easier for others to know what's happening in the database easier.
Thanks
The Fastest Methods aren't always the Quickest Methods
-
I think the important thing isn't what naming convention you use, but that you choose one and stick to it. There's been a lot of discussion here and elsewhere about whether you should use underscores, capitals, Hungarian notation, whether you name your tables in the singular and plural, and so on. My view is that it doesn't matter very much, as long as you're consistent.
John
-
Have a look here
http://databases.aspfaq.com/database/what-naming-convention-should-i-use-in-my-database.html
and just for fun...
http://www.thc.org/root/phun/unmaintain.html
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks 😀
The Fastest Methods aren't always the Quickest Methods
-
Mark-101232 (7/18/2011)
Have a look herehttp://databases.aspfaq.com/database/what-naming-convention-should-i-use-in-my-database.html
It's just me, I know, but I name my tables after what a single row of any given table contains. Part of the reason is for tables like "Customers". Inside the table, there may be a "CustomerName" column... if the current thoughts on a table containing a set prevail, then shouldn't the column also follow suit and be called "CustomersName"? Considering that a column should contain one and only one value and type of information for the entire column, such naming seems totally contrary (to me) to the fundamentals of a database.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (7/18/2011)
Mark-101232 (7/18/2011)
Have a look herehttp://databases.aspfaq.com/database/what-naming-convention-should-i-use-in-my-database.html
It's just me, I know, but I name my tables after what a single row of any given table contains. Part of the reason is for tables like "Customers". Inside the table, there may be a "CustomerName" column... if the current thoughts on a table containing a set prevail, then shouldn't the column also follow suit and be called "CustomersName"? Considering that a column should contain one and only one value and type of information for the entire column, such naming seems totally contrary (to me) to the fundamentals of a database.
I dunno... if the column was just Name and you had a Product table with Name and a sales report of products and customers, you'd need to alias both columns to something other than Name to join the tables. If both columns are strongly named with CustomerName and ProductName, then those writing queries already have a default name for both columns which carries along in the queries unless they do extra work to change the names. 🙂
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
Jeff Moden (7/18/2011)
Mark-101232 (7/18/2011)
Have a look herehttp://databases.aspfaq.com/database/what-naming-convention-should-i-use-in-my-database.html
It's just me, I know, but I name my tables after what a single row of any given table contains. Part of the reason is for tables like "Customers". Inside the table, there may be a "CustomerName" column... if the current thoughts on a table containing a set prevail, then shouldn't the column also follow suit and be called "CustomersName"? Considering that a column should contain one and only one value and type of information for the entire column, such naming seems totally contrary (to me) to the fundamentals of a database.
Interesting point, no idea what the answer is though. As has already been said, consistency is the key to this, personally I don't subscribe to any of the "my-standards-are-better-than-yours" arguments.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Jeff Moden (7/18/2011)
Mark-101232 (7/18/2011)
Have a look herehttp://databases.aspfaq.com/database/what-naming-convention-should-i-use-in-my-database.html
It's just me, I know, but I name my tables after what a single row of any given table contains. Part of the reason is for tables like "Customers". Inside the table, there may be a "CustomerName" column... if the current thoughts on a table containing a set prevail, then shouldn't the column also follow suit and be called "CustomersName"? Considering that a column should contain one and only one value and type of information for the entire column, such naming seems totally contrary (to me) to the fundamentals of a database.
Jeff it isn't just you. I do the same thing. I think of the Customer table as a collection of Customers. It also is more awkward for conversations. "Did you import those records into the Customers table?" or "Is that guy in Employees?". And then comes the challenge of what do you name something when it IS a collection of other entities. For example if you needed a collection of Customers and your base table was named Customers...does it become Customerss?
As everybody has said there is no right or wrong. What is most important is that your naming convention makes sense to you and that you keep it consistent.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Jeff Moden (7/18/2011)
It's just me, I know, but I name my tables after what a single row of any given table contains. Part of the reason is for tables like "Customers". Inside the table, there may be a "CustomerName" column... if the current thoughts on a table containing a set prevail, then shouldn't the column also follow suit and be called "CustomersName"? Considering that a column should contain one and only one value and type of information for the entire column, such naming seems totally contrary (to me) to the fundamentals of a database.Interesting point indeed, Jeff. I think part of the answer to your question is linguistic. A modifier (in other words a noun used as an adjective) is always in the singular, even if it describes more than one thing. (Please don't anybody flame me if you think of an exception to this rule!) This is why we have say "bottle bank" and "car wash". However, this doesn't explain why we don't name a column "Addresses" or "CompletionDates". I suppose it's just convention.
John
-
personally, the rules that i stick with are, for table names, try to keep them as descriptive as possible, even if it makes them a bit long. for the names of the columns, always prefix them with something associated to the table name. For example, if the table name were to be Customer, then the column names would all be prefixed by c_
beyond that, i used to always name my stored procedures sp_, until i read an article somewhere that said naming procedures as sp_ actually incurs a slight performance hit, due to the fact that the engine automatically looks at the list of system stored procedures first, before the user stored procedures, if it sees the name is sp_ (can't remember the article name). so, now i name them usp_. functions i always name as func_.
for the names of the stored procedures, i always include the name of the table being used as the basis of the stored procedure.
-
kramaswamy (7/18/2011)
personally, the rules that i stick with are, for table names, try to keep them as descriptive as possible, even if it makes them a bit long. for the names of the columns, always prefix them with something associated to the table name. For example, if the table name were to be Customer, then the column names would all be prefixed by c_beyond that, i used to always name my stored procedures sp_, until i read an article somewhere that said naming procedures as sp_ actually incurs a slight performance hit, due to the fact that the engine automatically looks at the list of system stored procedures first, before the user stored procedures, if it sees the name is sp_ (can't remember the article name). so, now i name them usp_. functions i always name as func_.
for the names of the stored procedures, i always include the name of the table being used as the basis of the stored procedure.
the article is a link above that you may be refering to 😀
The Fastest Methods aren't always the Quickest Methods
-
I don't treat naming database objects as distinct from naming any other kind of object. Code Complete by Steve McConnell has one of the best discussions on how to name objects I've read.
I've worked with a LOT of different (and sometimes downright bizarre) naming conventions, but to this day the easiest I find is just to call something what it is -- no prefixes, notations, etc.
I use pascal case (so NamesLookLikeThis) just because I find them easier to read, but that's really just a personal preference.
Also, I've moved to singular names; I used to pluralise table names to signify that they were a collection, but tools like SSRS work better with singular names, so no biggie. Besides, singular names are shorter.
I very rarely use abbreviations, unless they are VERY well known. So no Emp, Cust, No, etc.
One practice which I have moved TOTALLY away from is renaming tables within queries to have two letter prefixes. I've found this like moving backwards in time towards the old FORTRAN days or BASIC days when your variables could only have two letters. Instead, I use the name of the object or the role of the object as the name.
I also don't use block capitals for which many people would frown at me. Too many years as a C programmer! I find block capitals awkward to read, and I'd rather make my code more readable than less. Besides, keywords and reserved words all get coloured now, so it's not like block capitals are necessary to pick them out.
In the end, so long as your style makes sense and is consistent, that's all that matters. I've tried to make mine as consistent and as READABLE as possible, even if sometimes that means a little more typing. I find when I come back to edit code after a while, I always prefer that it's readable code.
Different people will give you different answers; pick what works for you.
-
Thanks everyone, I have changed some things I do as they didn't make sense but thought it was the right way to do them. 😀
The Fastest Methods aren't always the Quickest Methods
-
For the past several years I've stuck this convention: I name my tables after singular nouns, and if the primary key is a system assigned integer (ex: identity), then that column is named after the table plus underscore and 'id'. If the primary key is a standardized code, then I use the suffix _code instead. For example: order_id int versus state_code char(2), and I always use codes rather than integer identifiers, if one is available. For other columns, I don't use Hungarian notation, but I do use a handful of meaningful column suffixes like _name, _date, and _desc.
Cross reference tables are named by combining of two table names separated by an underscore, and the primary key is the combination of two primary keys (rarely is a new surrogate key needed here).
Rather than use CamelCase, I instead use underscores, and I generally code everything except literal strings and comments in lowercase.
create table customer(
customer_id int primary key,
customer_name varchar(180) not null,
active_from_date date,
active_to_date date
);
create table customer_rateplan
(
primary key ( customer_id, rateplan_id )
customer_id int not null,
rateplan_id int not null,
active_from_date date,
active_to_date date
);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply