Best Practices for Database Design
-
I do use "_" in stored procedure names but I prefice the name with the database (I know another abvr coming up).
So my Stored Procedure for pulling information from the employee table from the Human Resources database would be:
hr_employee_select[;n]
And modifying it would be:
hr_employee_modify[;n]
[;n] being optional and "n" refering to a specific Stored Procedure group
Imagination is more important than knowledge.– Albert Einstein
-
Not only do I do database development but I also code applications as well. I usualy throw on a datatype for a quick mental reference when coding and also I never have to worry about accidently using a reserved keyword.
As far as table ID's go I agree with Jeffery all the way!
Imagination is more important than knowledge.– Albert Einstein
-
Supplemental ideas, some that conflict with previous opinions.
I rarely start a new database from whole cloth, but tend to come in after the thing has been rolling for a while. I've found it works best if you follow the conventions that were used by your predecessors. I've encountered settings where two or more database developers have come in with the then-current standard (or thier own preferences), and the result is a confused mess of names. Was that table Promo_Ref, t_Promo_Ref, or tb_Promo.. or perhaps Ref_Promo_tb????
Given the choice, I tend to embed underscores in names. This tendency is because some major DBMS's don't keep track of camel-case, and LONGNAMESALLONECASE tend to be difficult to read.
-
I personally think that the spirit of the article is the most important take-away here. The point is that you should have a standard naming convention that is followed across your organzation - which is something that I completely agree with. I have worked with some extremely effective DBAs, and they had all of their standards documented.
That's not to say that standards can't evolve over time, but to evolve they must first exist.
Thanks for bringing this issue to the forefront!
Sincerely,
Chuck Heinzelman
-
It is interesting that this dicussion has gone on for two years. It is important to have a standard and to follow it.
SQL server is not picky about capitalization. Now go work with MySQL on a Linux box. Table names are case sensitive and must follow the rules for file names. I leave the research to the reader.
Then go work on an AS-400 system where all table names and field names are returned IN ALL UPPER CASE no matter how you typed them. After a few years of this you learn not to rely on capitalization.
It is part of our coding standard and since SQL server suports it we use capitailization rules in our standards.
As to reference tables (we call them domain tables) is is the frequency of data change that determines this. There are 50 states in the US. The table that holds state names and abbreviations is a domain table. There are not that many additions anticipated and no changes or deletions aer upcoming either.
As for strict naming rules, no one has yet mentioned J. D. Edwards World or One World. Hundreds of tables many of which have hundreds of fields. The naming rules are so consistant that I wrote a program that documented the data structure. There are doumentation tables including one to record documantation for installation specific fields.
ATBCharles Kincaid
-
I've been a DBA 20+ years; been writing SQL for almost 20, now.
I don't care. Yeah, you need a naming convention. It should be simple and consistent and mostly, IMO, YMMV, it's a matter of taste after that. InitialCaps versus underscored_names? ALL_UPPER_CASE versus all_lower_case? Initial_Caps_With_Underscores? (OK, that one's stupid.) But I still don't care. Pick one and move on. There's more important stuff to worry about.
Me, I name tables with the tbl prefix, views with v. I still, in a very limited sense, like hungarian notation that establishes broad data type of the given field -- "n" for number (int, bigint, numeric, decimal), 'm' for money, 's' for all strings, chars and varchars and etc. But I've lived without it and it's not a fight worth fighting if other people in your organization have a strong feeling about doing it a different way.
Fighting to root out dynamic SQL and live read/writes against table structure -- that's a fight worth fighting. At my current company we have a bunch of java programmers, who are about as vague on the concept of "database" as any group of programmers you'll ever meet -- bright guys, but the objects-relational argument lives on here, as elsewhere. I got a view layer created so that they weren't touching my table structures -- not ideal; I'd have preferred an SP layer -- but it does at least give me a code layer sitting on top of my structures, so that five years from now, when we change the name of a column, we don't have to remediate the 35 different apps that are all touching that column; we only have to provide a backward-compatible set of SPs and views and functions, instead, and that's doable. (In the other scneario, what you really get is the Permanent DBA Employment Act -- if you can't touch the database, you build more databases instead. And then someone has to monitor and maintain and back up and defrag and blah blah blah ... whereas, if one set of more-flexible structures can be maintained, a much smaller group of people can handle it.)
THAT was worth fighting ... but naming conventions? Eh.
-
AGREED!
I am pretty big on naming. I agree that any naming convention works, just stick with it! I do like to see naming conventions that at least employ some logic but consistency is the real issue.
Moving on I would agree that if we are going to pick a battle our individual taste on how to name something is certainly not the battle to pick.
Proper database architecture. Using Stored Procedures rather than in-line SQL (BIG ONE), using triggers as a last resort, and when used thought through! A trigger is not an automatic Stored Procedure! There are fire orders, bulk insert issues to consider, ect. On an error do you rollback, return? One must really consider the intent and the cause / effect when employing a trigger.
Stored Procedures don't write and forget. Optimization is key, are you using your indexes? Is the default locking level of SQL ideal for this query? I see so many stored procedures that utilize basic SQL without any thought to scale or effect on other objects. A database is a puzzle and each piece has an effect on the next.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
-
I would like to share a set of naming conventions that works well for me over the years. For table names, I mainly use domain naming technique and use suffix to categorize tables: Master table, Detail table, Lookup table, Cross reference table, and History. For Example
Order è Master
Order_Detail è Detail
Order_Type_L è Lookup (for table whose value rarely change)
Product_Type_Shipper_X è Cross reference between product type and shipper
Order_Hist è Order History
Domain naming technique allows related tables group together such as:
Order_Archived instead of Archived_Order
Order_Deleted instead of Deleted_Order
As for column names, I use what is called Type Class naming convention (I read this first in an IBM manual). Each column will be suffixed with one and only one type class. For example, CustomerID will become Customer_ID and CustomerName will become Customer_Name. The following is a list of Type Classes I used:
ID – Identity, refer to primary key.
Amt - Indicates amount, usually refer to dollar amount, Decimal Numeric type
Code - Indicates an arbitrary code, Alphanumeric type
Num - Indicates an arbitrary number, Numeric type (no decimal points)
Qty - Indicates quantity, Numeric type
Flag - Indicates true or false, Boolean type
Name - Indicates name, Alphanumeric type
Desc - Indicates description, Alphanumeric type
Addr - Indicates street address, Alphanumeric type
Obj - Indicates an object, Object type
Date - Indicates column contains date without time, Datetime type
Time - Indicates column contains time without date, DateTime type
DateTime - Indicates column contains date and time format, Datetime type
Above Type Classes fit 90% of column naming needs. For those odd column names that adding a Type Class will make them look awkward, I name them without Type Class. For example, fax number is Fax_Number (not Fax_Num because it is not a numeric field.)
This naming convention offers several advantages over other data-typed naming convention as well as no data-typed naming convention.
· Data-typed naming convention (such as using int, char, float, etc. as field name suffix) is platform dependent; thus it is not portable where as Type Class naming convention offers general types, not platform specific types.
· Data-typed naming convention while is programmer friendly but is not user friendly. Type Class name are much more meaningful to the users.
· An added advantage of using Type Class naming convention is that a Type Class name adds a qualifier to a field name making it un-ambiguous to the users. For example, a field named State is ambiguous because the user wouldn't know whether it is a State_Name or State_Code. A field named Customer is ambiguous because the user wouldn't know whether it is a Customer_ID, Customer_Code, or Customer_Name. This issue becomes important when the user started to run ad hoc queries. Often, the users have problems getting the result sets because they don't know the pattern of data stored; thus, don't know what criteria to use or whether or not to use quotes in the criteria. With Type Class naming convention, the user will know exactly what to expect in the result set by reading the field name.
-
I do not care about underscores one way or the other. There is one point missed when using capitalization. It concerns collations. If you are binary or case sensitive it can lead to many seemingly trivial issues that will cause lots of 'lost time'. The only easy way to avoid this issue is to make everything all lower case. Then, though, when using all lower case the underscore comes back in.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I've been working with Databases for over 10 years now and my biggest complaint of my co-developers is naming conventions. I'm a fairly fast typest and a few extra keystrokes for a name really only adds 2 seconds to the typing of a name. So I tell all the people who work for my company to type out everthing.
Column Names So your example of
acct_nbr. I'd get very upset with them and get them to Change it to AccountNumber, it makes it easier to read and you don't confuse people nbr (non breaking space
) For Table names I always do something like
{Product}_{Object}_{SubType} so something like So lets say I had a eCommerce Application called CCM, my table structure would be
CCM_Product <-- stores product data
CCM_Category <-- Stores category Data
CCM_Product_Category <-- Stores the relationship between product and Category
for Stored procedure
Another beef I have with stored procedure naming is that if I want to find all stored procedures related to a specific table or function I need to sift through all of them. Some may start with Get some with Update or something else. thus my convention is as follows
CCM_Product_Get <-- Gets a product
CCM_Product_List <-- Gets a list of Products
CCM_Product_AddUpdate <-- Does an add and and update if the productID being passed in doesn't exist
CCM_Product_Delete <-- obvious deletes a product
CCM_Product_Update_Prices <-- Sets Updates the prices
CCM_Category_Get
CCM_Category_List
CCM_Category_AddUpdate
Now this may be a small thing but some of my products have 100+ stored procedures. now 90% of the time I'm working with "Products" or I'm working with "Categories" thus this makes it so easy to get a list of them.
Views
One great thing about views is that to your queries it appears as they are a table. So I name them as Such. CCM_Postal_Codes or CCM_Countries. You may think these are bad names and would confuse the issue. But the usefull part of this is that I have a common database that stores items like this. So these views can query it directly by doing a "Select * from Common.dbo.Postal_Codes" and now to my application it'll not care.
The extra useful part of this is that if I move a database to another server in a different data center I can create a Table called CCM_Postal_Codes with exactly the same structure of the Common one and then delete the view. No code has to change or stored procedures.
Well thats my two Bits.
-
I suggest that next time name the article "(Best) practices for naming your database objects"
-
There is a best development and deployment practices presentation that runs around the internet. It is typically given to SQL User Groups and in Code Camps... Enjoy...
http://www.itresourcepartners.com/bssug/downloads/BestCodingPractices.ppt
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
-
Regarding "naming databases according to whether they are production, development".
What we do is use a place holder for databasenames (e.g. %%DBNAME%%) in our scripts and if we want to build a production database for example we use find and replace (well actually we do it in code) to handle a different database name. This allows us to store source controlled scripts, but keep the flexibility of requiring different db names for different purposes.
Thanks,
Alex Weatherall
-
Another physical modeling technique is using "class words". This involves using various suffixes based on the type of column. For example (underscore is optional):
_NUM Number
_QT Quantity/counts
_DT Date
_TX Text string
_IN Indicator
_TM Time
_AM Dollar Amount
_CD Code
So, Purchase Amount, Purchase Code, and Purchase Date would be:
Purchase_AM, Purchase_CD, Purchase_DT.
Enjoy! - Paul Foshee
-
Although prefixing table names to indicate the type of table has been a good practice, we have other options in 2005. Why not utilize schemas?
I proposed the use of schemas to aid in the identification of the type of database object. EG.
ref or lu - for reference or lookup tables.
snap - for data that is not near realtime, e.g. say loaded by batch where it basically was a snapshot
appl - for application supporting tables.
vw - for views.
secure - for sensitive and/or encrypted data.
Then tables in the dbo schema, are those containing transactional data.
We've even been kicking around using schemas for stored procs by function, like:
get, put, getref, putref, getsecure, and putsecure.
This would allow you to grant rights at the schema rather than object level.
Anyone else implemented this type of alternative schema alternative to prefix naming standards?
Viewing 15 posts - 106 through 120 (of 145 total)
You must be logged in to reply to this topic. Login to reply