Naming Conventions
-
Is there an industry standard naming convention for SQL Server objects?
I've looked at various articles on this subject and they all have slightly different formats.
I personally use:
tbl for tables e.g. tblCustomer
usp for stored procedures e.g. usp_StoredProcedure
udf for functions e.g. udf_Function
When naming fields I tend to use the capitalisation method (FieldName) over the underscore method (Field_Name). The disadvantage of the capitalisation method is that it is hard to read when the authors of SQL scripts don't bother capitalising the field names. The main reason I don't use the underscore method is that it can take longer to type (more characters to type). You may laugh at this reason but it does make a difference.
-
We all seem to have our own Naming Standards that we have developed over time. Have a look at this article as its close to my standards but has some additional good features..
http://www.sqlservercentral.com/columnists/sjones/codingstandardspart1.asp
If it aint broke don't fix it!
Andy. -
Part 2 of it
http://www.sqlservercentral.com/columnists/sjones/codingstandardspart2formatting.asp
My Blog:
http://dineshasanka.spaces.live.com/ -
No real stadnards and MS changes theirs over time as well. I'd pick something (mine or some others) and stick with it.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
The wonderful thing about standards is that everyone has different ones.
I'm glad that we are not forced to use them.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
-
Yes, I've seen everything under the sun too. I still like what I was told to use when I started my current, very long term project:
t_ for tables (fyi - table names should be singular as plurality is implied, e.g., t_table not t_tables)
v_ for views
p_ for stored procs
tI_, tU_, tD_, tIU_, tIUD_... for triggers
I tend to use underscores for readability. I also follow the convention I learned to end the field names with a type indicator. For example, _cd, _num, _id.
My best advice would be to try and come up with a standard and then have your company buy into it so that everyone follows it going forward. If it's just you, then buyin shouldn't be too difficult.
-
Naming convention depends on the aspect, the way you "see" your objects through your code.
If you have an IDE that doesn't differentiate between table, view, sp, function, you have to use prefixes to see what objects are used in the code.
I use a lot of TSQL and I personally don't need tbl prefix for table. It is obvious from the code that after FROM comes the table name. I do prefix views (vw_*) to distingush between tables and views since they can be used in the same way. I don't prefix stored procedures or UDF since it is obvius from the code what kind of an objet it is. I too don't like to type too much. And from name formating (capitals) I can determine if it is a field, table, ...
As mentioned before, think of something practical, write it down and stick with it.
-
Personally, I prefer to use suffixes: _TB, _VW, etc.
-
Hmm, just a thought:
What is the logical difference between data in a table and in a view? Why do you need to know that they are physically stored differently? What if you decide for performance to convert a view into a table, would the table then be prefixed with 'v' or 'vw_' to keep backward compatibility? Or rather a view called 'tbl_*' or 'tbl*'?
In alot of cases, why prefix them at all? To avoid proper documentation of your objects or is it just an old tradition from a programming language you like? If you already know that your object is a user defined function (you must know because otherwise you can't use it) why prepend it with 'usp_*'?
Please shed light on my curiosity people
Regards, Hans!
-
Let me throw in something from the lighter side of life. Get a cup of coffee, sit back, relax and read this excerpt from a post from Joe Celko (yes, who else dares to say this
in such a direct manner) to the MS newsgroups...
When we lived in caves, ate our young and wrote in FORTRAN I and II and
BASIC, we *had* to prefix variables with datatype information for the
simple compilers available back then to work. In FORTRAN, any name that
began with 'I' thru 'N' was an INteger and everything else was floating
point. In BASIC, any name that began with "$" was a string.
Flash forward to modern languages. They are most abstract and less
focused on the PHYSICAL implementations. Some languages are weakly
typed -- that is, a name can change data types during program execution
-- and some are strongly typed -- that is, a variable or other
structures keeps the same datatype and changes only values.
People who write in weakly typed languages often put a prefix or postfix
on names to tell them what the original data type was in case they screw
up and cause a change they did not mean to cause.
Old joke about weak data types:
Teacher: "Billy, what is 6 times 9?"
Billy: "ahhh, red?"
Teacher: "NO! , Sally, what is 6 times 9?"
Sally: "Thursday?"
Teacher: "NO! Tommy, what is 6 times 9?"
Tommy: "54."
Teacher: "Right! Now tell the class how you ngot the answer."
Tommy: "I divided red by Thursday!"
...
This also violates the ISO-11179 Standards for metadata. The basic idea
is that a data element is named for what it means in the data model. It
is NOT named for WHERE it is used (i.e. no table name affixes in column
names). It is NOT named for HOW it is used (i.e. no "pk" or "fk"
affixes ). It is NOT named for it physical representation (i.e. no data
type affixes).
A data definition shall:
a) be unique (within any data dictionary in which it appears)
b) be stated in the singular
c) state what the concept is, not only what it is not
d) be stated as a descriptive phrase or sentence(s)
e) contain only commonly understood abbreviations
f) be expressed without embedding definitions of other data elements or
underlying concepts
Name development begins at the conceptual level. An object class
represents an idea, abstraction or thing in the real world, such as tree
or country. A property is something that describes all objects in the
class, such as height or identifier. This lets us form terms such as
"tree height" or "country identifier" from the combination of the class
and the property.
The level in the process is the logical level. A complete logical data
element must include a form of representation for the values in its data
value domain (the set of possible valid values of a data element). The
representation term describes the data element's representation class.
The representation class is equivalent to the class word of the
prime/class naming convention many data administrators are familiar
with. This gets us to "tree height measure", "country identifier name"
and "country identifier code" as possible data elements.
There is a subtle difference between "identifier name" and "identifier
code" and it might be so subtle that we do not want to model it. But we
would need a rule to drop the property term in this case. The property
would still exist as part of the inheritance structure of the data
element, but it would not be part of the data element name.
Some logical data elements can be considered generic elements if they
are well-defined and are shared across organizations. Country names and
country codes are well-defined in ISO Standard 3166, Codes for the
Representation of Names of Countries, and you might simply reference
this document.
Note that this is the highest level at which true data elements, by the
definition of ISO 11179, appear: they have an object class, a property,
and a representation.
The next is the application level. This is usually done with a
quantifier which applies to the particular application. The quantifier
will either subset the data value domain or add more restrictions to the
definition so that we work with only those values needed in the
application.
For example, assume that we are using ISO 3166 country codes, but we are
only interested in Europe. This would be a simple subset of the
standard, but it will not change over time. However, the subset of
countries with more than 20 cm of rain this year will vary greatly over
time.
Changes in the name to reflect this will be accomplished by addition of
qualifier terms to the logical name. For example, if an application of
Country name were to list all the countries a certain organization had
trading agreements with, the application data element would be called
Trading partner country name. The data value domain would consist of a
subset of countries listed in ISO 3166. Note that the qualifier term
trading partner is itself an object class. This relationship could be
expressed in a hierarchical relationship in the data model.
The physical name is the lowest level. These are the names which
actually appear in the database table column headers, file descriptions,
EDI transaction file layouts, and so forth. They may be abbreviations
or use a limited character set because of software restrictions.
However, they might also add information about their origin or format.
In a registry, each of the data element names and name components, will
always be paired with its context so that we know the source or usage of
the name or name component. The goal is to be able to trace each data
element from its source to wherever it is used, regardless of the name
it appears under.
I recommned that tables be given collective or plural names, since they
are sets and not scalar. If the table has one and only one row, then
use a singular name. UPPERCASE the keywords, capitalize schema objects
(tables, views, procs, etc.) and lowercase scalars (variables, column
names, etc.) Use underscores which make reading easy and are portable;
do not use special characters like '#', '$', '[', ']', etc. which some
SQL vendors allow.
I like postfixes that tell me something about the semantics of scale
used for the column, as per the above:
"_id" = Identifier, it is unique in the schema and refer to one entity
anywhere it appears in the schema. Never use "<table name>_id" ; that
is a name based on location and tell you this is probably not a real key
at all. Just plain "id" is too vague to be useful to anyone and will
screw up your data dictionary when you have to find a zillion of them,
all different,but with the same data element name (you do have one,
don't you?).
"_date" = date, temporal dimension.
"_nbr" = tag number; do not use "_no" since it looks like the Boolean
yes/no value
"_name" = explain itself, nominal scale
"_code" = expect to translate this for humans.
"_total" = a sum, an aggregated dimension which is logically different
from its parts.
"_seq" = sequence, ordinal numbering.
"_tally" = cardinal number. Also called an absolute scale.
Then an application might have some special situations with units of
measurement that need to shown on an attribute or dimension. And ALWAYS
check to see if there is an ISO standard for a data element.
--CELKO--
--------------
Did I ever mention that I changed my mind and now really like this man
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank!
Nice text. At this end it is properly read, printed and started to be considered
Regards, Hans!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply