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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Good naming convention

Author  Topic 

SqlFriend
Starting Member

26 Posts

Posted - 2004-08-23 : 09:13:26
I am setting up a database and am curious what naming conventions you would suggest.

I have a table tblEmployee. I want to have a primary key (and index) which is an integer. Would you call this tblEmployeeID, or EmployeeID?

I have a table of tasks tblTask, which points back to the tblEmployee. Specifically, an employee creates a task,and an employee is assigned to a task. (And there will be other columns in tblTask for employees which all need to be kept distinct) Would you create keys like Creator_EmployeeID and Assigned_EmployeeID, or would you use CreatortblEmployeeID or Creator_tblEmployeeID or some other scheme?


Ideas welcome!

Thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-23 : 09:18:28
http://vyaskn.tripod.com/object_naming.htm

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-08-23 : 13:41:51
(Folk: this is a bit of a draft for blogging, so any thoughts, comments and critisisms are welcome! I know I've voiced much of this before, but I'm trying to consolidate it a bit more thoroughly this time around)

We use a notation of SYSTEM_SUBSYSTEM_NICKNAME_FullName for our tables (no "tbl..." prefixs etc)

So ACC_AR_CUST_Customer would be a table in the "ACCounts" system, sub-system is "AR" [Accounts Receivable], Table "nickname" is "CUST" and the descriptive name is "Customer"

All the columns will be in the style "ar_cust_Xxx" (we drop the "acc_" bit for the column names, which can lead to duplicate column names, but we try VERY hard to avoid duplicate column names).

So column names might be: ar_cust_Name, ar_cust_CurrentBalance, ar_cust_Status and so on.

We use the suffix "_ID" for an IDENTITY number [i.e. generated by our application], "_Code" for a 'natural key' code, assigned by the user, and "_Ref" for a 'Reference' provided by a third party. So conceivably for the ACC_AR_CUST_Customer table the PK would be ar_cust_ID or ar_cust_Code (and just possibly ar_cust_Ref if it was linked to some third party package!)

We sometimes use a GUID for the PK too (where data must be moved around amongst dis-connected systems) - we name those ar_cust_GUID.

If we also have a table ACC_AR_ORD_Orders, for Customer Orders, that that would have columns like
ar_ord_ID -- PK
ar_ord_Date -- Date the order was placed
ar_ord_ar_cust_ID -- FK Join to the Customer table

Note that for a FK Joined column we combin the prefixes of both tables to indicate what the correct JOIN syntax would be.

Wherever we use a variable to hold column data (e.g. in SProcs or VB) we use the exact same name as the column. If we then need to change the name, or the datatype / size, it makes it much easier to find all isntances where the column is used.

Anytime we see a column name "ar_cust_XXXX" we know exactly which table it belongs to - so statements like

DELETE D
FROM ACC_AR_ORD_Order AS D
JOIN ACC_AR_CUST_Customer
ON ar_cust_ID = ar_ord_ar_cust_ID
WHERE ar_cust_Status = 'DORMANT'

have inherent defensive-programming - if we accidentally use a column from the wrong table it sticks out like a sore thumb:

DELETE D
FROM ACC_AR_ORD_Order AS D
JOIN ACC_AR_CUST_Customer
ON ar_cust_ID = ar_cust_Age
WHERE ar_cust_Status = 'DORMANT'

We use Singular names for everything. Some would argue one way or the other on this, but we just decided that having a consistent rule was what mattered.

The first 5 columns in every table serve the same purpose:

EditNo - incremented on each edit (we use this for optimistic locking)
CreateDate - datetime record was created
CreateUser - ID of the logged-in user who created the record.
UpdateDate - datetime record was last modified ...
UpdateUser - ... and by whom

We use ARCHIVE tables for many of our main tables. These have exactly the same columns as the main table, but all the column names are prefixed by "A_". There are three additional columns (at the begining) for "identity", "action" and "datetime". We only store "deleted" records in the audit table - so in a UPDATE the original data is stored (the current record is available, in the main table, of course), so we store that as an "Update" action, whereas an actual delete is stored as a "delete" action. The Audit tables are maintained by triggers on the main table to which they refer.

The archive table names gain an "a" - ACCa_AR_CUST_Customer (this groups the Archive tables for a 'System' just underneath the corresponding Main Tables).

The Audit tables have all columns set to IS NULL and no constraints / foreign keys. (Allowing column requirement to change over time without requiring historical data to be amended / kept in step)

We also have "Import" tables which we use for Staging data (e.g. accounting information from a "back office" system that we need to use on a sales web site).

These have the same columns, and again no constraints etc. (an import routine may not be providing columns which are critical to our application ...). The columns for these tables have an "I_" prefix, and a couple of extra columns at the start of the table - a BatchNumber [from a centrally allocated table] to allow the actual merge of the data into the live tables to be synchronised cleanly, and a datetime when the record was inserted into the staging table.

The table names gain an "i" - ACCi_AR_CUST_Customer.

The Import tables are based on "delta" updating - so records are ONLY inserted/updated in the Import tables if they are actually different [based on the columns actually being updated] - that way only those rows that actually change in the Import tables are given the batch number / datetime of the update, and thus only changed rows are then merged into the main tables.

Kristen
Go to Top of Page

sampson
Starting Member

2 Posts

Posted - 2004-08-23 : 15:17:13

My only input to this topic would be that what ever convention on which you end up..... NEVER USE UNDERSCORES!!!!!!!!!!

They make code absolutely unreadable!!!

Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-24 : 08:03:36
Two schema propositions.
I suggest dropping the tbl prefix since it doesn't add value or meaning to the schema. Unless you want to differentiate tables from views etc. ( in which case the prefix "t" should be enough )

I prefer #1 ( but IMO both are Ok )


-- Schema proposition 1
create table Employee(
Id int not null,
EmploymentNumber int not null,
FirstName varchar(35) not null,
LastName varchar(35) not null,
Gender char(1) not null,
constraint PK_Employee_Id primary key clustered(Id),
constraint UC_Employee_EmploymentNumber unique(EmploymentNumber),
constraint CHK_Gender_FM check( Gender in('M','F') ) )
create table Task(
Id int not null,
Employee_Id_Creator int not null,
Employee_Id_Assigned int not null,
TaskName varchar(35) not null,
constraint PK_Task_Id primary key clustered(Id),
constraint UC_Task_TaskName unique(TaskName),
constraint FK_Task_Employee_Creator foreign key(Employee_Id_Creator) references Employee(Id),
constraint FK_Task_Employee_Assigned foreign key(Employee_Id_Assigned) references Employee(Id) )

-- Schema proposition 2
create table Employee(
EmployeeId int not null,
EmploymentNumber int not null,
FirstName varchar(35) not null,
LastName varchar(35) not null,
Gender char(1) not null,
constraint PK_Employee_EmployeeId primary key clustered(EmployeeId),
constraint UC_Employee_EmploymentNumber unique(EmploymentNumber),
constraint CHK_Gender_FM check( Gender in('M','F') ) )
create table Task(
TaskId int not null,
EmployeeId_Creator int not null,
EmployeeId_Assigned int not null,
TaskName varchar(35) not null,
constraint PK_Task_TaskId primary key clustered(TaskId),
constraint UC_Task_TaskName unique(TaskName),
constraint FK_Task_Employee_Creator foreign key(EmployeeId_Creator) references Employee(EmployeeId),
constraint FK_Task_Employee_Assigned foreign key(EmployeeId_Assigned) references Employee(EmployeeId) )

drop table Employee
drop table Task


/rockmoose
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-24 : 08:22:16
Doing some datawarehousing then Kirsten ?!

I think that: "we drop the "acc_" bit for the column names" will add confusion in the schema since the names then will not carry all the information, and you will have to refer to the table name to get the SYSTEM info.

Also I think that all the prefixing on the column names is unnecessary, compare columns:
ACC_AR_CUST_Customer.ID /* enough information */
ACC_AR_CUST_Customer.ar_cust_ID /* duplicate info in naming convention */

If the column is to be used in a FK relation the naming of the column would be:
ACC_AR_ORD_Order.ACC_AR_CUST_Customer_ID /* better IMO */
ACC_AR_ORD_Order.ar_ord_ar_cust_ID /* more confusion imo */

More .... hopefully constructive criticism ;-)
SYSTEM_SUBSYSTEM_NICKNAME_FullName IS a bit Heavy...
We use:
SYSTEMCODE[DATABASECODE]_Fullname, where the [denotes optionality] and the the SYSTEMCODE, DATABASECODE are 3 letter abbreviations for the source systems & source databases. DATABASECODE is optional in case the system has multiple databases/catalogs which we have to track.
Example naming:
ACCARC_Customer /* "ACC" ACCounts system, catalog in ACC is "ARC" Accounts Receivable */
ACC_Customer /* If the Accounts system is not complex enough to track catalogs */

cheers,
/rockmoose




Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-08-24 : 12:33:47
Partly it comes about because the people working on the "ACCounts" system may not have anything much to do with, say, the "CRM" developers. We drop that first part from the columns 'coz its just too much to type in and cart around, but yes it would be better to keep it and have it complete.

The key thing we are trying to achieve is that the column name is unique within the database, and thus we cannot accidentally attribute a column to the wrong table, and when we see a column name in some code we know which table it came from.

I agree that ACC_AR_CUST_Customer.ar_cust_ID is information overload, but in practice we never write that. We would normally do:

SELECT ar_cust_ID
FROM ACC_AR_CUST_Customer

or

SELECT AR_CUST.ar_cust_ID
FROM ACC_AR_CUST_Customer AS AR_CUST

or even use some other, more brief, alias. The second example is still duplicative of course, but actually I find that reassuring - like Hungarian notation - as I code I am self-checking that I am marrying the right columns with the right tables.

An Alias name for a table is more likely, in our code, when the same table is referenced more than once and would otherwise be ambiguous, so:

SELECT INV_ADR.ar_adr_Address1, DEL_ADR.ar_adr_Address1 ...
FROM ACC_AR_ORD_Order
-- Invoice address
JOIN ACC_AR_ADR_Address INV_ADR
ON INV_ADR.ar_adr_ar_cust_ID = ar_ord_ar_cust_ID
AND INV_ADR.ar_adr_Type = 'INV'
-- Delivery address
JOIN ACC_AR_ADR_Address DEL_ADR
ON DEL_ADR.ar_adr_ar_cust_ID = ar_ord_ar_cust_ID
AND DEL_ADR.ar_adr_Type = 'DEL'

but I can see that in coding shops where full prefixing with the table name, or a "verbose" alias, is used that has the same effect (and if used 100% of the time then mismatch of columns & tables is caught by the parser)

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-24 : 12:43:55
I'm suprised that the Dr. has weighed in...

As far as unique column names go...would you make a FK reference to another table a different name?



Brett

8-)
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-24 : 12:52:52
Brett,

" I'm suprised that the Dr. has weighed in..."

clarify, or am I just daft ;-)

/rockmoose
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-24 : 15:14:10
column naming and prefixing...

this is just plain painful, ouch!

select
ar_adr_ID,
ar_adr_cust_ID,
ar_adr_Address1,
ar_adr_Type,
....
from
ACC_AR_ADR_Address /* ok have just minor problems with this */


I can't envision a business requirement that makes unique column names necessary.
Making column names unique for less errorprone code, no I don't think that will hold in court .

To be fair...
A formal naming convention that works is great and hard to criticise. ( since it works )
The sharpest criticism for the "heavy indexing approach" would be one of unvwieldability and unreadability.
Personally I would strive for a simpler and more readable naming convention.

Sample SQL is in order

ACC - ACCounts system,
ARC - sub-system is "AR" [Accounts Receivable] /* 3 char abbr */

SELECT IA.Address1, DA.Address1 ...
FROM ACCARC_Order ORD
-- Invoice address
JOIN ACCARC_Address IA
ON IA.Customer_ID = ORD.Customer_ID
AND IA.AdressType = 'INV'
-- Delivery address
JOIN ACCARC_Address DA
ON DA.Customer_ID = ORD.Customer_ID
AND DA.AdressType = 'DEL'


Edit - Code tags

Feel free to flame me,
/rockmoose
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 15:24:53
quote:
Originally posted by rockmoose


I prefer #1 ( but IMO both are Ok )




I hate #1. Column names should be the same in all tables if they have the same meaning. So you shouldn't have Id in one table and EmployeeId in another table if they have the same meaning. With #1, you'll have a lot of columns named Id but they all have different meaning. Forget that they all refer to identities. Id of Task does not have the same meaning as Id of Employee. But Id of Employee has the same meaning as EmployeeId in another table, so name them the same.

IMO, of course. But my hatred for #1 has stemmed from working on systems designed like this.

Tara
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-24 : 16:10:16
quote:

With #1, you'll have a lot of columns named Id but they all have different meaning



Probably at most as many columns named Id as the number of tables [;-)]
Employee.EmployeeId or Employee.Id ?
Consistency in the naming scheme is very important and I think that #1 and #2 hold equal value.

Which is preferred is a matter of personal preference.
My preference for #1 stems from habit :-),
and also from experience with this systems designed this way which imo, are quite practical and not contradictory to good design and naming convention.

I do see your point though in calling same things, same thing !

/rockmoose
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 16:13:17
In my code, it would be e.EmployeeId as I use aliases to shorten it. So what is confusing with #1 with aliases is e.Id and v.Id and s.Id. You then have to look at the aliases to see what it is referring to.

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-24 : 16:22:15
quote:
Originally posted by tduggan

In my code, it would be e.EmployeeId as I use aliases to shorten it. So what is confusing with #1 with aliases is e.Id and v.Id and s.Id. You then have to look at the aliases to see what it is referring to.

Tara



I always code every column in the select, join or predicate with an alias (except for single table ops) to make it self documenting...


And I meant to say...

I'm suprised the Dr. has not weighed in...

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=6859


Jeff prefers


SELECT ThisIsTheNameOfAVeryLongTable.Col1
, ThisIsTheNameOfAVeryLongTable.Col2
, ThisIsTheNameOfAVeryLongTable.Col3
, ThisIsTheNameOfAnotherVeryLongTable.Col1
, ThisIsTheNameOfAnotherVeryLongTable.Col2
, ThisIsTheNameOfAnotherVeryLongTable.Col3
FROM ThisIsTheNameOfAVeryLongTable
FULL OUTER JOIN ThisIsTheNameOfAnotherVeryLongTable
ON ThisIsTheNameOfAVeryLongTable.Col1 =
ThisIsTheNameOfAnotherVeryLongTable.Col1



Sorry Jeff








Brett

8-)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 16:23:40
The horror!

Tara
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-24 : 16:24:07
True Tara,

You would have to know what You are aliasing when you join tables, and mostly this is not a big problem.
On the other hand e.Id = s.EmployeeId /* you know that s (fk table) references e (pk table) .Edit typo. */

I would happily design a system either way.
And work with either one, as long as the naming is Consistent !!!

/rockmoose
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-24 : 16:30:42
No seriously,
Tables and columns should be sequentially named

select
t001.c001,
t001.c002,
t001.c007,
t002.c021,
t002.c018,
t042.c003,
t042.c001
from
t001
join t002 on t001.c001 = t002.c018
join t042 on t002.c021 = t042.c003

just have a proper metadata catalog [;-)]

/rockmoose
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-24 : 18:55:24
lol@rock. Wow...that brings back some nightmares.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-08-25 : 01:37:43
"As far as unique column names go...would you make a FK reference to another table a different name?"

Sorry, I haven't grasped the meaning.

I do use, for example, ar_inv_ar_cust_ID for a column in the "Accounts Receivable - Invoice" table that joins to the "Accounts Receiveable - Customer" table - on the "Customer ID" column.

The name of my Foreign key would be FK_ACC_AR_INV_ACC_AR_CUST_ID - which is made up of the "nicknames" for the "Parent" table, the "Child" table, and the descriptive part of the child table's column name - where the FK is a multi-part key I use the first column name, and on the odd occassions where that is not unique I use a tie break.

The FK name looks hard to read, but given that we are dealing with the System/Subsystem/TableNickName names all the time it breaks into its parts, and we are obviously used to it.

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-08-25 : 01:45:23
"I can't envision a business requirement that makes unique column names necessary."

Indeed, it isn't necessary, but years of programming has taught me to use defenive programming. I like [a cutdown version of] Hungarian notation.

For example, I wouldn't leave out the final ELSE

IF @foo = 'Y'
BEGIN
... some actions ...
END
ELSE
IF @foo = 'N'
BEGIN
... some actions ...
END
ELSE
BEGIN
... some error logging and probably application termination action ...
END

and our Table/Column naming convention is an extension of that. It just reduces errors in the code IME. (But I'd be the first to admit that while I think it works well for us, it is not for everyone, and the most important thing is a convention that is consistent)

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-08-25 : 01:51:37
We have a JOIN style which is often the "wrong way round" relative to most code that I see, just in case it adds to the debate!

SELECT *
FROM MyTableA A
JOIN MyTableB B
ON B.bCol1 = A.aCol1
AND B.bCol2 = A.aCol2

in that we put the Column names for the JOINed table MyTableB on the left.

Most times we are trying to match ALL the PK columns on the joined table, so this makes it easier to see (particularly in code review) that all PK columns in MyTableB have been satisifed

Kristen
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -