YANT - SQL Server Forums

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

Site Related Forums

The Yak Corral

YANT

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-23 : 13:16:52

YANT = Yet Another Naming Thread

This oughta be a poll.

What's the consensus on the naming the PK-ID field in a table?

I've been using [TablenameID] myself, but I wonder if using [ID] would be simpler and not as confusing as I might have thought at first.

The same question extends to fields like CreateDate, UpdateDate and other columns that commonly appear accross all tables in a database.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-23 : 13:24:12

Qualifying columns with the name of the table (or it's mnemonic, as in db2) is old school....

an ID is So misused...

They're really codes in most cases

I guess it's born out of IDENTITY (another one to add to the list)

I like it when they say...ID_TYPE_CD

Talk about misleading

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-23 : 13:40:42

I do it both ways.

This way I confuse everyone equally.

Jim
Users <> Logic

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-23 : 14:06:29

quote:
Originally posted by X002548

Qualifying columns with the name of the table (or it's mnemonic, as in db2) is old school....

an ID is So misused...

Brett - I'm thinking you may be right about it being old school, but what kind of naming for a PK column do you use?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-23 : 14:10:33

I like short names so:

Id
aId, pId, cId

I'll occasionally use a ____Id if it is a widely used Id:
nodeId, siteId

Corey

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-23 : 14:24:51

StateCD
AccountCD
SSN
CompanyName
TaxAgecyCD
Region
Sub_Region
ReturnType
Add_TS
Add_By

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-23 : 14:30:41

We use TableName_ID. I hate ID as the column name. Of course either way is for IDENTITY columns. For non-IDENTITY PK columns, I use whatever makes sense to the application for the column names.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-23 : 15:16:17

Having the tablename as part of the column has helped us troubleshoot a lot here. When you have over 2000 tables and thousands of columns, it gets a little tedious to figure out which ID this ID is referring to. Of course, some of you will just say I'm lazy. You're right.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-23 : 15:28:19

Now I'm leaning back toward TablenameID, but is it inconsistent to treat CreateDate and UpdateDate columns differently (without the tablename prefix) - or is PK ID 'special'.

Not a big problem. The forum is about evenly divided.

Maybe votes should be weighted according to the member's post count?

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-23 : 15:34:28

Sam it's an opened question...

Use Natural Keys, not surrogates...no need for myTable99_ID

Unless you need one, the use it...

As far as the other attributes, just identify them as they are Updated_By, whatever.

Derrick, did you mean just for id's?

And Sam, this is a General SQL server question...

NOT a corral question...

Here's one...

Why is David to make 2 more Posts?

I say he's afraid of what custome title graz will give him...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-23 : 16:14:03

I think it's important to put the table names in the IDENTITY column name. CreateDate and UpdateDate aren't used in the joins usually and they have different data in different tables. The meaning is still the same but the data is differrent.

I just find it hard to read (my example is easy but imagine with lots more joins and a where clause):

SELECT ...
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.ID = t2.Table1_ID

If a column is storing the same data in a different table, then it should have the same column name. So if you name it Table1_ID in the child table, then name it Table1_ID in the parent table.

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-23 : 16:23:31

I've come full circle.

Then, I'm always spinning wheels.

Thanks all.

Sam

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-23 : 16:23:51

Agree with Tara........again.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-06-23 : 20:21:29

We use the suffix:

ID = Identity (or similar sequential-ish machine-assigned number)

Code = Alpha code such as GB, US

Ref = Externally provided alpha-numeric "reference" over which we have no control

All columns in a given table start with a common "nickname"

Columns which are JOINed incorporate the name of the Parent column

So, Country table might be:

ctry_Code -- e.g. GB
ctry_Name -- e.g. United Kingdom

and Address table might be:

adr_ID
adr_Address1
adr_Address2
adr_Address3
adr_Address4
adr_PostCode
adr_ctry_Code

Column names are unique, within the application (not just the table).

The column name is used globally - "@ctry_Code" for SProc parameters and temporary variables, and "ctry_code" in application code; thus a change to size/type/name of a column can easily be accomodated with a global find based on the name.

A JOIN would look like

FROM ADR_Address
JOIN CTRY_Country
ON ctry_Code = adr_ctry_Code

note that the repetition of "ctry_Code" makes it easy to check that we are comparing Like-with-Like

EDIT: I capitalised some stuff
EDIT2: Added a JOIN as a further example

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-23 : 20:41:16

I use <tablename>_id - one of the few places I use an _ in an identifier.
It means that you can use the same name for foreign keys in other tables - if you just call it id you would have to give the same data item a different name in other tables (makes it easaier to cut and paste in queries :)).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-23 : 23:09:20

I've done it the way Kristen does it at one place I worked. Every table had a "key" that was prefixed on all columns. It was nice from a domain and data dictionary perspective. Where I work now, the data modeler uses:

tablename_descriptor
Anywhere we do use an ID to identify the field, It's called tablename_key.
Again, it's made troubleshooting much easier in many cases.
We can also add to the _key when used as a foreign key. For example:

party_key_loan_officer
party_key_primary_borrower

This allows us to still search the entire database for anywhere we have a party key, but provides functionality to the users and developers as well.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-24 : 07:25:35

Coming from a Datawarehousing background - our ID columns are what we call surrogate keys and the keys that production systems call their id's we call production keys. So our Identity columns(surrogate Keys) are prefixed with dwh_ and our production keys are suffixed with codes.

DWH_Branch and BranchCode are columns in the Branch Table.

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources