February 27, 2003 at 7:11 am
Here are a few things we do which no one has mentioned:
Regarding the id field, we use ID when its an autonumber which will be meaningless to users (eg intCallID); No when the number generated will be used by users (eg intWorkOrderNo); and Code when its an alphanumberic string (eg strSerialCode, although the interface still says serial number).
We do prefix our table names. tbl for an entity table; tlkp for a lookup table (a table whose primary function is to translate a code into a description); and trel for a junction table. Since none of the circumstances that would warrant the use of a view apply in our shop, almost of our data retrieval is through stored procs.
We prefix column names to the VB type. This has some downsides, and when we create databases in .NET, we may have to relook this, but as a programming DBA I find it helps my programming significantly.
Just some ideas, but they work for us.
February 27, 2003 at 7:54 am
quote:
As an expert and consultant for Databases and SQL, I always give this standard :....
CREATE VIEW V_CUSTOMER_CST
AS
SELECT CST.PRS_ID AS CST_ID,
PRS_NAME AS CST_NAME,
TTL_TITLE AS CST_TITLE,
PRS_BIRTHDATE AS CST_BIRTHDATE,
CST_COMPANY_NAME
FROM T_CUSTOMER_CST CST
INNER JOIN TG_PERSON_PRS PRS
ON CST.PRS_ID = PRS.PRS_ID
LEFT OUTER JOIN TR_TITLE_TTL TTL
ON PRS.TTL_ID = TTL.TTL_ID
...
PLEASE, do not forget that SQL Server is CASE SENSITIVE with SQL names !
Sorry but your naming convention is pure horror for my ears. IMHO it belongs to the past that I rather avoid if I can.
SQL Server is case sensitive only if installed that way. We started this way but found out it is not a good idea to differentiate between data just because it was entered low or upper case, especially for key values this can be a problem. The same problem is with search...
I think naming convention should first be well documented and used within limits of that definition (until it is extended).
I'm a database developer and in my experience the same entity should have the same name in every table, except when there are several occurences of the same entity in the same table. Choosing the right naming can be extremely helpful in writing SQL code. From a name I can easily differentiate if it is a table, view, or a view that points to another database, field, entity identifier or surrogate key. Using prefixes to determine object type gives you a lot of extra keystrokes, but not much benifit. In SQL server it is fairly easy to differentiate SP, UDF and table by the way they're used. Table and view are used in completely the same way, so I use vw_ and xvw_ (for external view) prefix. I use first capitals in table names in plural just because one table contains several entities (records). One record contains ony one attribute in every field, so it's logical to use singular names. I also use all low case letters in field names. Field name tells me if it is generated or natural key. Also tables are prefixed with low case 3-letter prefix to differentiate lookup, system, temporary, work tables from the main tables (no prefix). This improves readability in EM.
February 27, 2003 at 8:31 am
Just my opinion:
I have a mainframe DBA background (now sql server) and have seen all kinds of things over the years from max 8 characters in Assembler to whatever you want. I've even seen columns called one thing but used for something completely different. Of course, there is no meta data available so you don't know where it's from and where it goes. (Lots of horror stories here.)
I am astonished, however, that with all the standards organizations around these days no one has published a paper on database naming conventions. (Perhaps they have, but I have yet to see anything comprehensive for SQL.)
It would be nice if the vendors created a standard and shipped it with the docs as part of the "getting started" routine.
I do know, however, that if you don't consciously develop and enforce a standard you will eventually follow the "quick and dirty" method and things will get out of hand fast.
I also know, there is always a "better way" to name things. So my recommendation is just settle on something and use it. This is far better than nothing. And because your asking the question, you're on the right path and ahead of the game.
February 27, 2003 at 8:50 am
It would be useful if someone could come up with a naming standard that can grow as databases evolve i.e. to take us through to SQL Server 2050, ORACLE 69i, Sybase 25, dBase VI etc.
I've seen standards come and go and not always for good reasons. I want a standard that can be easily understood by old and new developers alike and that naturally covers the needs of a wide range of languages.
A standard that applies to a specific niche is no good to me because I have to use a wide variety of tools.
If a standard can cover many languages/RDBMs then the eventual migration of the application is made so much easier by having a common frame of reference.
February 27, 2003 at 9:45 am
Case Sensitivity of SQL Server names -- This depends on the character sort order for the database. The default is a "dictionary" sort order which ignores case. You can see SQL Server Books On-line, also Andy Warren's article on this site "Worst Practices - Making Databases Case Sensitive (Or Anything Else)" [http://www.sqlservercentral.com/columnists/awarren/worstpracticesmakingdatabasescasesensitiveoranythi.asp].
Note that in the XML world, tags are case sensitive, and lower-case is the preferred; otherwise the same as SQL. Since XML is getting built into SQL Server more and more, this is a valid concern while you're talking about column naming.
"Id", "No/Num" and "Code" - Typically, "Id" is for non-arithmetic (whether numeric or not) values which identify but do not describe an entity. This means its a good candidate for autogenerating, but it may also come from outside the enterprise. "No" or "Num" is for arithmetic numeric values, or Identifier like "Social Security Number" which should be Id but everyone calls Num. "Code" is for values which are descriptive of the entity, but need translation to provide a meaning.
On the subject of "universal" and "vendor" naming standards -- I can hear the howls of the people with legacy databases and applications stuck with a new, incompatible standard that doesn't match their legacy. Also, my shop probably has some different needs than your shop; best we can do is raise the issues and explain some of the ramifications, and you can weigh these against your particular concerns. What's convenient for DBAs, VB programmers, Access developers, end-users using something like Access or Crystal Reports, PowerBuilder and Java are all different; here's hoping none of us have to worry about all of these!
David Lathrop
Data Architect
WA Dept of Health
David Lathrop
DBA
WA Dept of Health
February 27, 2003 at 9:54 am
quote:
What's convenient for DBAs, VB programmers, Access developers, end-users using something like Access or Crystal Reports, PowerBuilder and Java are all different; here's hoping none of us have to worry about all of these!
With the exception of PowerBuilder I have to worry about all of the above and in addition iDocs, server side Javascript, Perl, PHP, Vortex Script, VC++, XML and about 6 web content management systems. That's before you get into the RDBMS's and the really obscure stuff.
Its all very well the .Net people getting sniffy over a particular standard, but what do you do when you have a range like the above to look after?
February 27, 2003 at 11:46 am
Since everyone else is giving their $0.02...
I wrote a database standards guide and here is a part ot it dealing with columns names
I REALLY disagree with naming the PK column ID. I have some tables that have 10 FK. Coming up with new names for each one is idiotic.
General Column Naming Gguidelines
Generally follow the convention of:
[MEANINGFUL_NAME]_[SUFFIX]
The foremost rule is that column names should be meaningful. Avoid generic meaningless names like USER, DATE, or XGTS. Using the table name as a prefix is not necessary as the column is inextricable linked to the table and this makes the column make awkward. A possible exception to this guideline is foreign keys. It is a common practice that foreign keys have the table alias in name. If the convention outlined below is followed where the Foreign Key is names BASETABLE_ID, the owning table will be clear without having to look at the DRI.
Column names must be unique within the table and (except for Foreign Keys) are generally unique throughout the database. If the name is repeated in multiple places in the database, consider whether your meaningful name is meaningful enough. It is not an egregious error if two fields have the same name as long as they do not contain the same data!! The audit columns are an example of where it is a good idea to have columns with the same name in many tables.
Other guidelines for column names:
-Ensure that all column names are spelled correctly.
-Do not use any Oracle or T-SQL reserved word as a column name (see Appendix)
-Column names should use a logically consistant datatype. A column named LAST_UPDATE_DATE should not be a VARCHAR2
-Use NVARCHAR2 for most fields with text data. The NCHAR datatype should not be used unless the data really is a fixed length text value. Some people insist that all flag fields are stored as booleans where as other define a flag as a CHAR(1) and store Y or N.
-NUMBER columns should use correct precision, e.g. NUMBER(14). Don't use NUMBER without a precision. This is because the NUMBER datatype defaults to NUMBER(max), where max is system dependent. Migration to a new machine could affect the database size and performance.
All columns in all tables should be NOT NULL except for when the value of the data should correctly be expressed as NULL. A field such as PASSWORD_LAST_CHANGED_DATE should be NULL unless the password has actually been changed. Setting this value to an arbitrary data (such as January 1, 1900) can be misleading.
Use Unicode datatypes, like NCHAR, NVARCHAR, or NTEXT, if your database is going to store not just plain English characters, but a variety of characters used all over the world. Use these datatypes only when they are absolutely needed as they use twice as much space as non-Unicode datatypes.
Standard Suffixes
It is useful to use a standard suffix to identify common types of fields. The use of these is optional, but a consistent approach should be used throughout a given application. They should always be used in "Corporate Tables".
_CODE: a code that is known and used by a user to identify an object (usually an alphanumeric code), e.g. MFG_CODE.
_ID: A system generated unique number used internally within the application and not usually known by the user. This is usually generated by a sequence. For primary key columns, the column name will be <table alias>_ID; for foreign key columns, the column name will be <referenced table alias>_ID.
_FLAG: A field used as a Flag field to indicate a specific sub-type or role as identified during the analysis phase.
_DATE: Always postfix a date field, i.e. use SHIP_DATE, not DATE_SHIPPED
Some people use generic suffixes like _TX, _DT, _NUM, and _NM in column names. This is a bad idea for several reasons. It is better to not abbreviate; RDBMS use an Entity Short Name internally. The name is for the benefit of the user; make it as unambiguous as possible. Suffixes like _TX, _DT, _NUM, and _NM do correspond to actual datatypes and do not carry size information. This makes their usefulness limited.
Audit Columns
Audit Columns should exist on every table. They may have other names then those listed below; just be consistent throughout the database. These columns are as follows:
CREATED_DATEDATEDEFAULT SYSDATENOT NULL
CREATED_BYCHAR(32)DEFAULT USER NOT NULL
UPDATED_DATEDATE NULL
UPDATED_BYCHAR(32) NULL
February 27, 2003 at 12:00 pm
READING A BOOK ENTIRELY IN UPPERCASE WOULD BE EXTREMELY ANNOYING BECAUSE YOU COULDN'T FIGURE OUT WHAT WAS BEING EMPHASIZED. LIKEWISE, READING SQL STATEMENTS AND STORED PROCEDURES WHEN EVERY TABLE AND FIELD NAME IS IN UPPERCASE_WITH_ARCANE_NAMING_CONVENTION_UNDERSCORES WOULD BE LIKEWISE DIFFICULT TO UNDERSTAND OR SEPARATE KEYWORDS FROM EVERYTHING ELSE.
February 27, 2003 at 1:47 pm
Interesting topic and I have seen some really bad ideas.
Never, NEVER prefix databse objects with their type (i.e. tblEmployees, colLastName, spCalculateInvoice). Anyone monkeying around in the database at that level better understand what type of object they are dealing with. Not to mention that SQLServer navigates you to these objects via the type anyway (expanding treeviews).
Never prefix your columns with the table they reside. This a hold over from the old COBOL days when everything seemed to have a numeric prefix. You shouldn't need the column name to understand what table it is in.
Never use reserved words for object names, that is why they are called "reserved". Sure you can [get away with it] but why bother?
Save hungarian notation for coding not object naming.
As far as table/column names go I like a simple approach: Tables are plural and if they have a single key column, the key is a singular version of it. Table: Employees, Key column: Employee. Simple and easy to remember. Associative tables are a combination of the table names (i.e. EmployeeCompanies comprised of two FK columns: Employee and Company) This is only a suggestion and it is a very graceful and easy to remember but just a suggestion. Follow the above rules for the Don'ts and you should be OK.
February 27, 2003 at 7:16 pm
Like anybody needs any more suggestions...
1. Field names all caps - if you happen on a case sensitive situation you will not go crazy.
2. Primary key as ID_TABLENAME. Foreign keys then need not be prefaced with Fk or the like - you know because it has ID prefix but isn't the primary key. Suffix rather than prefix which can sometimes get cut off from view in the development environment.
March 1, 2003 at 9:40 am
I would like to offer my thoughts based on lessons learned with past experiences.
In my work as a developer I also have to manage the design and distribution of a database for customers using my comany's products. I inherited this database which had been designed consistently using Hungarian notation for table, table column names, stored procs, etc. In some ways I'm all for that if only because, for example, when you are working on a highly complex query it is useful to see whether the data is coming from a table or a view. However, as time has gone on our database has evolved; schema changes have been necessary both to maintain database performance and to serve the requirements of applications using the database. The 'problem' I have had is that the database has had to remain compatible with old versions of our applications. This has meant that where table schema changes have been made, a view has been used to present the table as it used to be. This involves changing the name of the table and creating a view with the old table name. This means that now we have views with a tbl prefix which is misleading. Not only that but in a couple of places column data types have been changed by the column names have had to stay the same so again the column name prefixes become misleading. Something to think about when considering naming conventions maybe?
March 3, 2003 at 8:17 am
Interesting thread! I wish there were an easy answer to the question of what standards to use for naming conventions, but...
Since there are different requirements for different systems, I don't think we will ever see an overall naming standard for database names. For example, in my situation (which may be unique), some of the requirements I was under when I created the databases for our system were:
1) the naming convention had to be protable over different DBMSs. Currently on SQL Server and Oracle. May go to DB2 or others.
2) Since the main database was broken down into a number of "subsystems", there was the possibility that there would be the same tables in a number of these susbsystems. For example, the Manage subsytem has a Node table as does the Conversation subsystem.
3) There were going to be a large number of foreign keys relative to the number of tables. The main database currently has around 125 tables and about 300 foreign keys.
4) There are 3 inter-related databases. That is, there is a static database, a dynamic database, and a databases that is used to create the data for the static databse. All 3 databases have versions of various tables.
So, when the databases were designed, the naming standards were based on these requirements. Could these standards be used for other databases? Maybe. Should they be? Probably not. But, they do work in this situation.
So, my feeling on the question of database standards is that there should definitely be standards, but they need to be based on the requirements of the application. At least as they are known during the design phase.
March 3, 2003 at 2:31 pm
Sorry jpipes, but I have to disagree with you about case and Underscores in names.
Capitals and underscores look consistant in multiple IDEs; mixed cases are harder to read.
I format my my SQL in SP to make it readable; that's why whitespace is ignored
The standard isn't arcane; it's just not what YOU do. Get over it. Your suggestion is no better or worse than mine.
Many (dare I say most) people who are Architects or Sr Developers have responsibilities in both the MS and Oracle world. This is a standard that works will in both. Having an Oracle standard and a different SQL Server standard is really difficult. This is a nice solution that works for developers.
quote:
READING A BOOK ENTIRELY IN UPPERCASE WOULD BE EXTREMELY ANNOYING BECAUSE YOU COULDN'T FIGURE OUT WHAT WAS BEING EMPHASIZED. LIKEWISE, READING SQL STATEMENTS AND STORED PROCEDURES WHEN EVERY TABLE AND FIELD NAME IS IN UPPERCASE_WITH_ARCANE_NAMING_CONVENTION_UNDERSCORES WOULD BE LIKEWISE DIFFICULT TO UNDERSTAND OR SEPARATE KEYWORDS FROM EVERYTHING ELSE.
March 4, 2003 at 1:52 am
There is quite a bit of disagreement here with regard to naming conventions.
We are all in agreement that a naming standard is a good thing, but there is an absence of a formally recognised standard.
I think the thing to do would be to approach this from a different dimension and ask "What do we want to achieve in implementing a standard"?
I would suggest that, with the provisos below it doesn't matter what standard you come up with, but you should stick to it.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply