June 20, 2008 at 2:22 am
If only everybody followed a naming convention. Generally this type of discussion sparks a lot of passion since we all have our own style. Essentially...
- generally I don't care what the convention is used as long as it's clear and most importantly consistent
- Underscores between words, capitalisation of words again is just style. You should choose the one which most suites your applications since some reporting applications will automatically replace underscores with spaces reducing the cost of implementation. To blindly enforce capitalisation in this instance just because "this is our standard" is a bit silly.
- some techy's don't like typing but abbreviating too much can create confusion or a database that's hard to intuitively pick up
Domain naming for tables is a very good thing to do. In building warehouses and ETL loads I have found the ability to create schema is great for this. e.g. All the tables or views used for reporting are placed in the report schema. It all becomes very clear and easy to manage using schema to group objects the are provided for a specific function.
Naming conventions are good... and a matter of style...
So don't get too hung up on it just pick 1 and stick to it!
June 20, 2008 at 3:06 am
Hi - Interestingly I've never seen any of those prefixes before, and I've been with over 10 organisations in Australia. Ones I've seen are lu for LookUp, jn or lnk for Junction or Link tables, vw For Views, tbl for Tables.
Other comments I'd say is I don't like under_scores either, and avoid abbreviations because of the issue a previous poster mentioned about obscure letters being used. (What makes sense to you, doesn't necessariy make sense to someone else).
Also, never use plurals, so it isn't Groups it is Group.
June 20, 2008 at 3:07 am
This is maybe one of the worst database convention articles I've read. Very misleading in places.
June 20, 2008 at 5:33 am
Markus Engelhardt (4/6/2005)
and if you try to insert something into a view the error that will pop up will clearly explain the difference.
Ummm.... what makes you think you can't insert something into a view? There are, in fact, updateable views if you follow a couple simple rules.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2008 at 6:11 am
Good article.
When working on apps of my own I follow my own special naming convention.
When developing for work I tend to follow my Lead's style because it makes it easier for the whole team, since that's what they're accustomed to looking at.
------------
why the 'usr' on stored procedures? I know not to use 'sp_' because of a full scan of the sys procedures, but I tend to prefix the calling app name like this 'UI_getAllUserData_sp'
June 20, 2008 at 6:17 am
Grant Fritchey (4/6/2005)
Naming standards are very good things. Common abbreviations are very good things.
Very good things can, however,be taken to silly extremes. We've got a logical modeling team tasked with, along with maintaining an enterprise logical model, establishing naming conventions. Unfortunately, these people work in the ethereal world of logical modeling, exclusively. They don't have to write code against the models they create, so naming every single table in a database Policy...* is not a problem for them even when we're talking about a couple of hundred tables, all inside a database named Policy. Establishing incomprehensible abbreviations like 'ddltbl' for deductible (do abbreviations generally add letters? note the new 'l') doesn't seem to slow down their work at all. The rest of us, dba's and developers,have been driven insane trying to get them to use an abbreviation like 'org' for organization instead of 'orgntzn'. I have, on more than one occasion, brought a dictionary over to their desk and pointed at common abbreviations defined with along with a word to no avail.
Don't get me wrong. I am in favor of practing a common approach. Just make sure the common approach makes sense.
Oh, and underscores in object names suck.
Man, do I ever agree with that!
Even though this article is 3 years old, lemme add a couple of things...
This is NOT Oracle... we DON'T have the limit of 30 characters for object names! While it's good to have a published and available list of abbreviations, abbreviating words like Account or Number or Service just aren't necessary. For example, how many different abbreviations have you seen for "Number"? Num, Numb, Nbr, Nmbr... What's that do for you? Save 2 or 3 characters only to have to be maintained on some standard abbrevieation list? Spell the bloody thing out... let aliases and good formatting take care of the rest.
I also agree about underscores... people want abbreviations to save space and then they use underscores! If you think about it, it actually takes less time to type Mixed Case than it does underscores, unlike Oracle, the casing is preserved, and it's just as or maybe more readable than the underscores.
I also agree about what was said about designers... yes, they need prefixes such as "Policy" or "Order" or whatever because they don't really have a good way to identify the schema or the database that something may be in... but don't name your tables that way... what if they need to be moved?
Views? I used to thing is was good to preface views with a "v" just to make it easier for developers to find... but it's really not a good idea... We started one thing out as a table... then, we decided to change it to a view... after a bit, we decided it should be two tables and the view would be repointed to the most current... then, we decided it should be a synonym, instead. If we had prefaced the name with "v" or worse yet, "tbl", we would have had to change a bunch of interface, stored procedure, function, Hibernate Mappings, and some (ack!) embedded GUI code and then would have had to retest all that stuff just to make sure we didn't screw something up. Instead, all we had to do was make sure that each "change" returned the same result set as an "object" and that was all.
So far as the article goes... ummm... sure... the title was a wee bit misleading. And, is the subject a rehash of something that almost everyone knows? Ummm... sure... BUT, just like many other articles, including many of my own, points are covered and there are a surprising number of folks (most are forum "lurkers") that just don't know these things. It's good to see articles that remind old dogs of the basics and newbies of stuff they may have never known otherwise. As always, there's a ton of additional information to be found in the discussions for each article.
Last but not least, I would find it very difficult to write an article about formatting and naming conventions... these are very nearly religious subjects that many individuals either take a great amount of pride in or are religious about writing their code as fast as they can without regard to any format or convention. If such things are to be followed, they must be both well published and they must be enforced. Remember that enforcement requires code reviews and it shouldn't be just the DBA that does code reviews... the DBA should be the final check only and the easier folks make the readability of the code, the better the DBA is going to treat you and the easier it will be to troubleshoot the code in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2008 at 6:41 am
I've followed conventions such as those mentioned in this post, and from an organization perspective, it makes sense.
However, I read somewhere, and please correct me if I'm wrong, that as of SQL Server 2005, it is better not to prefix tables in this way, since you could use a schema for that. The reasoning I got was that it adds processing when querying.
apparently, and again, I could be wrong about this, the system searches on the first letter of the table first, then the second letter, then the thrid. if all tables are prefixed with tbl, for instance, then there are at least three checks for any table you are trying to query. While this is barely noticeable for most queries, if a system is being queried many times, this can take its toll.
If I remember correctly, separating these items by schema allows for the naming convention to provide a context, without adding that overhead.
Can anyone confirm or reject my understanding of this?
June 20, 2008 at 6:54 am
Good article.
We also use prefix in the table names indicating module or subsystem. That's a very useful technique. We use 3 or 4 letters as a module prefix and in english it would be something like this:
CUS_ for customers related tables
SUPL_ for suplliers subsystems
this solves that in different contexts a word (table name) may mean different things.
Thanks, Marcos.
June 20, 2008 at 7:45 am
Grant Fritchey (4/6/2005)
By the way, the title of this article is misleading. I expected to see something about natural versus artificial keys, unique constraints, resolving sub-type roll-ups, logical arcs, denormalization by design, defining the cluster index as part of table design, establishing clustering strategies as part of database design. You know, database design. Instead it was a decent piece of work on establishing standards in naming. Not bad, just a surprise.
What he said 🙂
Naming conventions are great, and the important thing about them is not specifically what they are but that they are consistently used. Having a naming convention is a best practice but WHAT the convention is is not.
I worked one project where the naming convention was total overkill (and useless)- they appended the first four letters of the database name to everything (why, when each project had its own database anyway???) then had cryptic four letter combinations for every possible type of object, then three letter combinations for the type of activity (if a stored proc or function)... it was a pain. This was a clear case of someone imposing standards just to seem rigid without thinking about usability or the whole point of having the standards.
When I have the freedom to define the convention I go for simple but obvious.
TableName
TableNameType (if it is a type lookup)
TableNameHistory or TableNameAudit (for history/audit tables)
TableNameJoin (for join tables)
TableNameDetail (for supplementary detail tables)
I prepend "vw" to my views, "usp" to my procs, "fn" to my functions, "tr" to my triggers.
Proc naming:
uspGetAllSomethings
uspGetSomethingByID
uspInsertSomething
uspUpdateSomething
uspDeleteSomething
uspSaveSomething (if it is a combo insert/update type proc)
uspDoSomeOtherWeirdThing (if it isn't CRUD based)
No underscores anywhere, and few abbreviations. I type really fast so extra letters don't bother me and I'd rather see the whole word than try to remember what I was trying to abbreviate.
But as I said, there isn't necessarily a right or wrong convention - the purpose is to be consistent and even more important UNDERSTOOD by the other folks trying to use your db.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
June 20, 2008 at 8:10 am
Using naming conventions is definetly something that we should welcome in databases.
I do agree with most of the stuff that you say but except for one point. If we prefix the names with the conventions, we can see all of them as groups - history tables, refereance tables, etc in enterprise manager. But instead of prefix, its better to have a suffix for the terms ref, hist, etc. The reason is that when we are searching for tables in enterprise manager, suppose the table name is "users", when we click on the key "u" in key board, we should be able to go to the table directly. I was onto this problem when I did my first design with the prefixes.:)
According to what I read from the previous comments, it also gives us some performance boost as the table names are not starting with same letters.
June 20, 2008 at 9:02 am
Here's a naming option some people are really going to hate:
Mostly use mixed case to name your tables. Examples:
. FacilityPerson
. AbuseReferral
. StateSawFacility
However, use underscores occasionally to help make a table easier to read, especially when there are several tables with very similar names. For example, I chose NOT to do these names in a database that generally follows the above convention.
. LegalConnectRisk
. LegalConnectLog
. LegalConnectAbsRef
With the beginning part the same for all three tables and the name being so long (and several other tables starting with the text 'Legal', I find the following easier to read:
. LegalConnect_Risk
. LegalConnect_Log
. LegalConnect_AbsRef
I don't mind "mixing" conventions when it makes sense. (It's not really mixing. It's just a more complicated convention.) And this does to me. Note: over 95% of the tables in my databases do *not* have underscores.
June 20, 2008 at 10:46 am
Nice article with very good points.
I would like to add that using table prefixes allows us to have a subset of tables show up together in table view.
Also, the audit fields I use are more cumbersome at times but perhaps more complete:
add_datetime
added_by
add_pgm
edit_datetime
edited_by
edit_pgm
These fields have saved countless hours in debugging.
June 20, 2008 at 12:12 pm
Wow, Adam and DC should did earn a lot of points during this discussion...
June 20, 2008 at 1:50 pm
Adam Machanic (4/6/2005)
Tables and views should be plural. Attributes should be singular.
How does this rule apply to a table of sheep that a stud farm maintains?
June 20, 2008 at 3:53 pm
Good article.
I use VIEW_ to prefix all views.
I completely agree on naming fields EXACTLY the same from one table to another (if they refer to the same data). I don't even put suffixes on the field name - ID exists as ID in many of my tables.
I also believe ERD's :w00t: are in order to define how all your tables are related, with a repository where the ERD's are stored. I know this may take a little extra time, but as with everything else in this field, get in the habit and it becomes second nature. ERD's provide your successors/co-programmers some help in decrypting your naming conventions/database decisions when you're not around. I haven't seen any forums or articles about ERD's since I subscribed to sqlservercentral.com.
Viewing 15 posts - 121 through 135 (of 145 total)
You must be logged in to reply to this topic. Login to reply