Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1112131415»»»

Best Practices for Database Design Expand / Collapse
Author
Message
Posted Friday, June 20, 2008 2:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 22, 2008 1:42 AM
Points: 3, Visits: 12
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!
Post #520437
Posted Friday, June 20, 2008 3:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 22, 2008 5:42 PM
Points: 1, Visits: 8
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.


Post #520464
Posted Friday, June 20, 2008 3:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 22, 2012 3:07 AM
Points: 48, Visits: 100
This is maybe one of the worst database convention articles I've read. Very misleading in places.
Post #520465
Posted Friday, June 20, 2008 5:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #520552
Posted Friday, June 20, 2008 6:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:06 AM
Points: 1, Visits: 10
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'
Post #520590
Posted Friday, June 20, 2008 6:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #520593
Posted Friday, June 20, 2008 6:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 24, 2011 7:50 AM
Points: 41, Visits: 83
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?
Post #520619
Posted Friday, June 20, 2008 6:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 21, 2010 9:50 AM
Points: 24, Visits: 41
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.
Author of Nautilus for sql server.
Post #520628
Posted Friday, June 20, 2008 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 19, 2013 7:41 AM
Points: 118, Visits: 381
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
Post #520683
Posted Friday, June 20, 2008 8:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 22, 2011 6:50 AM
Points: 29, Visits: 19
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.
Post #520711
« Prev Topic | Next Topic »

Add to briefcase «««1112131415»»»

Permissions Expand / Collapse