Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2011-09-13 : 17:26:41
|
| Hello,What naming conventions are most common today for stored procedures, tables, views?Currently, I am using StoredProcName_sp, ViewName_vw and no trailing characters for tables.Thanks, John |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-13 : 19:28:14
|
I agree. And I always like saying that if you can't recognize 'em in context then you have much bigger problems  |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-14 : 03:35:32
|
We have "Modules" within our code. For example we might have an eCommerce module, and a Membership Module. They have mnemonic names such as "EC" and "MEM"Tables have a two-part mnemonic for "Area" of the module, and "Table name". For Orders we need a Header and an Item table, and these are part of the "Sales Order Processing" area. So we have the two tables:EC_SOP_ORDH_OrderHeaderEC_SOP_ORDI_OrderItemThe names include both the mnemonics and "Human Readable Name". Table names use singular rather than plural.All columns within the table start with the mnemonic prefix, although we don't repeat the Module (dunno why, just decided we didn't need to, sooner or later we will have a duplicate ...)sop_ordh_ID - Order Header IDsop_ordh_Total - Order Total Valuesop_ordi_Item - Item within the ordersop_ordi_sop_ordh_ID - Foreign key to Order Headerthus all columns within the database are unique, this makes it easy to Find & Replace or Find & Modify code. All parameters and application code that refer to a column include the column name - e.g. @sop_ordh_Date_START and @sop_ordh_Date_END - so that global search on "sop_ordh_Date" will find all possible locations in the code where a change may have ramifications.CRUD Sprocs are named EC_SP_SOP_ORDH_Get, _Save, _Del, _Find.Others have named based on the table of their primary function:EC_SP_SOP_ORDH_DailyStatsWe tend to have VIEWs that encapsulate the common single-table lookups - where a Code joins to a table to get a DescriptionEC_SP_SOP_ORDH_View would contain a JOIN to the Customer table and return columns for First / Last name etc. Such columns are prefixed with "V_" and include the source-column's name:sop_ordh_cust_reg_ID is the Order Header table's column that joins to Customer Registration table by its ID (i.e. the customer number). The View would have columns:V_sop_ordh_cust_reg_ID_FirstNameV_sop_ordh_cust_reg_ID_LastNameetc. matching the columns commonly used from the Customer table.When we have a JOIN the intention is that the naming convention makes it easy to spot incorrectly joined columns, so:FROM dbo.EC_SOP_ORDH_OrderHeader JOIN dbo.EC_SOP_ORDI_OrderItem ON sop_ordi_sop_ordh_ID = sop_ordh_ID is self documenting as a "valid" join, whereas:FROM dbo.EC_SOP_ORDH_OrderHeader JOIN dbo.EC_SOP_ORDI_OrderItem ON sop_ordi_stk_prod_ID = sop_ordh_ID is not! (stk_prod_ID is the Stock Product ID). Bit like the reasons for using Polish Notation and the like. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2011-09-14 : 11:00:06
|
quote:
Originally posted by jbates99
Currently, I am using StoredProcName_sp, ViewName_vw and no trailing characters for tables.
Generally frowned upon these days. Don't specify the object type in the object name.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
|
|
|
|
Object Naming Standards