SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Schema Naming Conventions


Schema Naming Conventions

Author
Message
ckempste
ckempste
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3147 Visits: 1
Not again you sigh... Smile

Well, its on for young and old and im in the thick of it yet again. Table and column naming conventions are what I am after as far are recommendations and tried and tested schemas. I have gone for the following for sometime now:

Tables : name as required to represent the business, eg. trade
P.Keys : post-fix with _id, eg. trade_id
F.Keys : the joining column is listed as part of the column name

eg. traderto_trade_id this example means the table “trade_rto” joins to “trade” over “trade_id”

The first portion (column prefix) should shorten to <= 8 characters in length with no spaces or underscore characters. For example:

Employer => emp_<column name>
Employer_site => empsite_<column name>
Organisation -> org_<column name>

The 8 character restriction is for the tables prefix, not the entire length. Even so, the entire length should not exceed 30 characters if possible.


Another schema design we have in another app database uses the following:

The other uses a slightly different approach. The only difference in this method is the naming of columns, which is especially evident in the key-column referencing. For example:

Training_Product ----------------------- Course
Training_product_id (pk) Training_product_id (fk)

In this case, as the training product table is the parent, all other referring tables use the same primary key column name for the foreign key column name. This is a common scheme, but unfortunately does not allow a developer to “follow the relationship model” from one table to the next and thus, for large data models, can be time consuming when creating large complex queries. Another problem is that extensive use of table aliasing is required in such a scheme.


I have also heard of schemes where the data type of the column is in the naming of the column. This seems very strange and something I wouldnt like to explore for a variety of reasons.

What I havent gone into is issues of replication, and inter-related schemas with the same table names.

Ideas?

Cheers

Chris


Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: Moderators
Points: 26117 Visits: 2749
Im definitely against any data type desc in the column name. Where I work, we have an app that uses "foreignkey" for all the foreignkey cols. Guess what it calls the primarykey cols! It's not a bad system. Overall, I like to use the colname of the primarykey as the col name of the foreignkey column.

No spaces in object names! No spaces in object names! No spaces in object names!

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25848 Visits: 1917
Where I work, we follow a naming convention to what Michael Hotek proposes. Here's the link to the article on his site:

http://www.mssqlserver.com/articles/naming_conventions_p1.asp


K. Brian Kelley
bkelley@agfirst.com

K. Brian Kelley
@‌kbriankelley
Steve Jones
Steve Jones
SSC Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: Administrators
Points: 151390 Visits: 19455
I developed a standards document similar to Michael Hoteks, but more in line with my habits. I am a fan of abbreviations and Proper case, but I have seen some good arguements to use more descriptive names.

I have an article (not posted yet) on naming standards, but basically here are mine:

Tables: descriptive name of data being stored (customer, products, etc.). Suffix of _lu for lookup tables.

Columns:Use standard abbreviations (set forth in company document), proper case. Ex, EmpID, CustID, ProdSKU, OrdDt

PK: <table name>_PK

FK: <source table>_<dest_table>_FK. If more than one FK, then FK1, FK2, etc.

Indexes: <table name>_IDX for clustered index. If this is PK, PK rule takes precedence. Non clustered indexes are <table name>_NDX1, _NDX2, etc.

Triggers: Haven't done much with instead of triggers, but my standard held over from v6.x is tr<purpose><table name>. Ex: triCustomers (insert trigger for Customers table), triuEmp (insert, update trigger for Emp).

Defaults: df<name>. descriptive name

Rules: don't use them. Suggest r_

User Defined Datatypes: Not used

Stored Procedures:
sp<purpose><descriptive name>. Ex. spIns for inserts into single table, spAdd for inserts into multiple tables. I have a series of 12 or so purposes spelled out in the document.
For DBA procs, dbsp<descriptive name>








Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Gopal Sharma-464186
Gopal Sharma-464186
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 23
We follow pretty much the same as Steve mentioned, only significant difference being the stored procs. For stored procs we use p_ .
For user defined procs, we should always start (depending on convention) with anything but sp coz all system procs are sp and SQL Server it seems takes longer (prob fraction of second but yes Smile) to locate our proc starting with sp. we also prefix purpose by few common operations we do ...viz populate/clean/verify/move follwed by actual purpose in short.

Thanks
Roy Ernest
Roy Ernest
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9033 Visits: 6891
Gopal Sharma (2/19/2009)
We follow pretty much the same as Steve mentioned, only significant difference being the stored procs. For stored procs we use p_ .
For user defined procs, we should always start (depending on convention) with anything but sp coz all system procs are sp and SQL Server it seems takes longer (prob fraction of second but yes Smile) to locate our proc starting with sp. we also prefix purpose by few common operations we do ...viz populate/clean/verify/move follwed by actual purpose in short.

Thanks

Gopal, it is not sp that is the problem. It is sp_. There is a big difference between sp and sp_. sp_ are system procs.
As for the naming convention, we use more or less the same as Steve mentioned. The difference being for index it will be idx_tablename__columnName. Just one under score additional.

-Roy
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search