Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

My t-sql teacher told us that naming our PK column "Id" is considered bad practice without any further explanations.

Why is naming a table PK column "Id" is considered bad practice?

share|improve this question

closed as not constructive by Jim G., Walter, gnat, Dynamic, Giorgio Dec 28 '12 at 11:13

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

19  
Well, it is not a description actually and Id means "identity" which is very self explanatory. That is my opinion. –  Jean-Philippe Leclerc Oct 17 '11 at 16:48
38  
I'm sure there are plenty of shops that use Id as a PK name. I personally use TableId as my naming convention but I wouldn't tell anyone its THE ONE TRUE WAY. It sounds like your teacher is just trying to present her opinion as widely accepted best practice. –  user29776 Oct 17 '11 at 16:58
18  
Definitively the type of bad practice that isn't that bad. The point is to get consistent. If you use id, use it everywhere or don't use it. –  deadalnix Oct 17 '11 at 17:00
38  
You have a table...it's called People, it's got a column, called Id, what do you think the Id is? A car? A Boat? ...no it's the People Id, that's it. I don't think it's not a bad practice and it's not necessary to name the PK column anything other than Id. –  jim Oct 17 '11 at 18:17
17  
So the teacher gets in front of the class and tells you this is a bad practice without a single reason? That's a worse practice for a teacher. –  JeffO Oct 19 '11 at 14:30

19 Answers 19

up vote 167 down vote accepted

I'm going to come out and say it: It's not really a bad practice (and even if it is, its not that bad).

You could make the argument (as Chad pointed out) that it can mask errors like in the following query:

SELECT * 
    FROM cars car
    JOIN manufacturer mfg
        ON mfg.Id = car.ManufacturerId
    JOIN models mod
        ON mod.Id = car.ModelId
    JOIN colors col
        ON mfg.Id = car.ColorId

but this can easily be mitigated by not using tiny aliases for your table names:

SELECT * 
    FROM cars
    JOIN manufacturer
        ON manufacturer.Id = cars.ManufacturerId
    JOIN models
        ON models.Id = cars.ModelId
    JOIN colors
        ON manufacturer.Id = cars.ColorId

The practice of ALWAYS using 3 letter abbreviations seems much worse to me than using the column name id. (Case in point: who would actually abbreviate the table name cars with the abbreviation car? To what end does that serve?)

The point is: be consistent. If your company uses Id and you commonly make the error above, then get in the habit of using full table names. If your company bans the Id column, take it in stride and use whatever naming convention they prefer.

Focus on learning things that are ACTUALLY bad practices (such as multiple nested correlated sub queries) rather than mulling over issues like this. The issue of naming your columns "ID" is closer to being a matter of taste than it is to being a bad practice.


A NOTE TO EDITORS : The error in this query is intentional and is being used to make a point. Please read the full answer before editing.

share|improve this answer
1  
@Chad, it was a reference to the fact that in that particular query you used 3 letters for aliases to table names, even when it made no sense (cars -> car. Thank God--you saved my fingers). Don't read too deeply into it. –  Stargazer712 Oct 17 '11 at 17:32
2  
worse than my alias names, was my mixing of plurality cars and manufacturer. One is plural, the other isn't. If people want to pick at the db, that's the bad practice that should be picked upon. –  CaffGeek Oct 17 '11 at 17:34
3  
I think it is bad practice. Obviously, as far as bad practice goes it's not terrible.. but it's so easy to avoid, why not do so? Now, I agree, for an intro class, is this the thing to focus on? Probably not.... –  user606723 Oct 17 '11 at 19:41
2  
@user606723, actually, I think for an intro class it's an important thing to make a point of. Teaching best practices should be fundamental. It's not until you're experienced that you understand the consequences, and tradeoffs and when you should deviate from best practices. –  CaffGeek Oct 17 '11 at 19:53
3  
@Chad, Agree to disagree. Trying to teach students "best practices" without allowing them to understand why those are the best practices is a futile effort. And since you can't cover everything, glossing over this point with "you just shouldn't do it, you'll figure out why later" is pretty sane choice from a professor's standpoint. Curious students can post on here or hopefully find this question already answered. (Or ask after class) –  user606723 Oct 17 '11 at 20:01

Because when you have a table with a foreign key you can't name that foreign key "Id". You have table name it TableId

And then your join looks like

SELECT * FROM cars c JOIN manufacturer m ON m.Id = c.ManufacturerId

And ideally, your condition should have the same field name on each sides

SELECT * FROM cars c JOIN manufacturer m ON m.ManufacturerId = c.ManufacturerId

So while it seems redundant to name the Id as ManufacturerId, it makes it less likely that you have errors in your join conditions as mistakes become obvious.

This seems simple, but when you join several tables, it gets more likely you'll make a mistake, find the one below...

SELECT * 
    FROM cars car 
    JOIN manufacturer mfg
        ON mfg.Id = car.ManufacturerId
    JOIN models mod
        ON mod.Id = car.ModelId
    JOIN colors col
        ON mfg.Id = car.ColorId

Whereas with proper naming, the error sticks out...

SELECT * 
    FROM cars car 
    JOIN manufacturer mfg
        ON mfg.ManufacturerId = car.ManufacturerId
    JOIN models mod
        ON mod.ModelId = car.ModelId
    JOIN colors col
        ON mfg.ManufacturerId = car.ColorId

Another reason naming them Id is "bad" is that when you are querying for information from several tables you will need to rename the Id columns so you can distinguish them.

SELECT   manufacturer.Id as 'ManufacturerId'
        ,cars.Id as 'CarId'
        --etc
    FROM cars 
    JOIN manufacturer
        ON manufacturer.Id = cars.Id

With accurate names this is less of an issue

share|improve this answer
108  
Not sure this is a good enough explanation to me. There's nothing wrong with saying SELECT * FROM cars c JOIN manufacturer m ON manufacturer.Id = c.ManufacturerId. I have used id for years and never found what you described to be a real problem. –  Stargazer712 Oct 17 '11 at 16:56
35  
I would say that the bad practice here is to alias tables with name like mfg or mod. manufacturers.id = cars.manufacturer_id is very readable and the error will stick out too. –  deadalnix Oct 17 '11 at 16:59
2  
@Chad > I already got problems with dumb variable names. Many times. For the reccord, here what I would say to a dev that does this in my team « mfg doesn't mean manufacturer, it means you are to lazy to type manufacturer ». –  deadalnix Oct 17 '11 at 17:12
8  
@Stargazer712: Doing SELECT * from 2 tables gives 2 x ID columns. ID is now ambiguous: do you reference by ordinal or name? SELECT * is not good practice either. Poor arguments. Fragile code. Chad is correct: defensive coding basically –  gbn Oct 17 '11 at 18:23
5  
@gbn, again, all ambiguity is gone if you simply do SELECT manufacturer.id FROM .... Every difficulty resulting from id can be overcome very easily, making it simply a matter of taste. –  Stargazer712 Oct 17 '11 at 19:25

Ruby's ActiveRecord library and Groovy's GORM use "id" for the surrogate key by default. I like this practice. Duplicating the table name in each column name is redundant, tedious to write, and more tedious to read.

share|improve this answer
16  
+1 for "and more tedious to read." - naming conventions shouldn't be thought of as a band-aid for sloppy code, they should be improving readability as a primary concern. –  Slomojo Oct 18 '11 at 1:30
2  
ID is far more tedious to read –  HLGEM Oct 18 '11 at 20:20

Common or key column names like "Name" or "Id" should be prefixed with the TableName.

It removes ambiguity, easier to search for, means far less column aliases when both "Id" values are needed.

A lesser used or audit column or non-key (say LastUpdatedDateTime) doesn't matter

share|improve this answer
31  
If you do this, I hate you for making me do extra typing!!!! The table's name is Person, what do you think the Id is going to be? Car? no, it's Person. –  jim Oct 17 '11 at 18:19
8  
@jim, I don't know about you, but typing 6 extra characters takes me roughly half a second. And considering I rarely ever select from one table, and thus would end up with two columns named 'Id' and will need to include the table/alias name anyhow, there is no savings in the number of characters typed. –  CaffGeek Oct 17 '11 at 19:13
11  
@Chad I find it superfluous. if I'm doing a join, c.id = m.manufacturerid, is ok with me. These columns are typically "mapped" to a class somehow, and to have a class with Person.PersonId makes me want to vomit...Yes,I am fully aware I have issues. –  jim Oct 17 '11 at 19:26
8  
@jim: Relational databases are not object databases. The fact that classes are designed a certain way doesn't mean that the same principles apply to databases. For example, m:m relationships are trivial between two object types and impossible between two tables without an association table. –  Adam Robinson Oct 18 '11 at 1:21
9  
I also disagree with this. Why stop at name and id? Why not have every column prefixed with its table name? It seems arbitrary to pick those two names to mandate a prefix. Conceptually, you must have the table in order to have the context of a column anyway. Why not just use that table name to clarify the query: Person.Name, Animal.Name, Part.Name,... –  Thomas Oct 18 '11 at 17:27

I don't consider it bad practice. Consistency is king, as usual.

I think it's all about context. In the context of the table on its own, "id" just means exactly what you expect, a label to help uniquely identify it against others that might otherwise be (or appear) identical.

In the context of joins, it's your responsibility to construct the joins in such a way as to make it readable to you and your team. Just as it is possible to make things look difficult with poor phrasing or naming, it is equally possible to construct a meaningful query with effective use of aliases and even comments.

In the same way a Java class called 'Foo' doesn't have its properties prefixed by 'Foo', don't feel obliged to prefix your table IDs with table names. It is usually clear in context what the ID being referred to is.

share|improve this answer
1  
Relational database tables are not classes. –  Adam Robinson Oct 18 '11 at 1:23
1  
They are however data structures and they're analogous to PODO classes. The same naming problems apply. –  Slomojo Oct 18 '11 at 1:28
2  
@Slomojo: No, they're not analogous to simple objects. Object-oriented design and database design are not the same, and are not even related. While they can, in some cases, yield similar (or even the same) design, that does not indicate that they are related. For example, m:m relationships are trivial in classes, but are impossible have between two tables without a third association table. –  Adam Robinson Oct 18 '11 at 1:55
1  
Quite how this relates to a naming strategy, I don't know. My analogy is (clearly?) only scoped to that extent. –  Slomojo Oct 18 '11 at 2:13
1  
I'm sorry my implied meaning wasn't very clear, I should have said "in this sense they are analogous to classes". Either way, I don't think being overly pedantic about this is particularly constructive. In terms of naming, tables and classes do share a significant amount of similarities. Best practices which develop in a cul-de-sac are fair game for revision, or at the very least are open to discussion. There's plenty within this Q&A that illustrate this effectively, I don't have anything else of note to add. –  Slomojo Oct 18 '11 at 22:36

This thread is dead, but I would like to add that IMO not using Id is a bad practice. The Id column is special; it is the primary key. Any table can have any number of foreign keys, but it can have only one key that is primary. In a database where all primary keys are called Id, as soon as you look at the table you know exactly which column is the primary key.

Believe me, for months now I've spent all day every day working in lots of big databases (Salesforce) and the best thing I can say about the schemas is that every table has a primary key called Id. I can assure you I absolutely never get confused about joining a primary key to a foreign key because the PK is called Id. Another thing that people haven't mentioned is that tables can have long silly names like Table_ThatDoesGood_stuff__c; that name is bad enough because the architect had a hangover the morning he thought up that table , but now you are telling me that it's bad practice not to call the primary key Table_ThatDoesGood_stuff__cId (remembering that SQL column names aren't in general case sensitive).

To be honest, the problems with with most people who teach computer programming are that they haven't written a line of production code in years, if ever, and they have no idea what a working software engineer actually does. Wait until you start working and then make up your own mind what you think is a a good idea or not.

share|improve this answer
1  
That's only the case if none of your primary keys is a composite key, which is, unfortuately, far too often the case. One should really only use surrogate keys in particular circumstances. –  nicodemus13 May 17 '12 at 16:32
3  
Using Id like that you end up with a situation where without thinking developers add primary key id to each and every table they make. One of the foundations of relational databases is the use meaningfull and aggregate primary keys and using id does not help. –  Pieter B Oct 20 '12 at 14:35

It makes it hard (and confusing) to perform a natural join on the table, therefore yeah, it's bad if not very bad.

Natural Join is an ancient artifact of SQL Lore (i.e. relational algebra) you may have seen one of these: ⋈ in a database book perhaps. What I mean is Natrual Join is not a new fangled SQL idea, even though it seemed to take forever for DBMS's to have implemented it, therefore it's not a new fangled idea for you to implement it, it might even be unreasonable for you to ignore its existence nowadays.

Well, if you name all your primary key's ID, then you lose the ease and simplicity of the natural join. select * from dudes natural join cars will need to be written select * from dudes inner join cars where cars.dudeid = dudes.id or select * from dudes inner join cars where dudes.carid = cars.id. If you are able to do a natural join, you get to ignore what the relation actually is, which, I believe, is pretty awesome.

share|improve this answer
2  
@Bill I do all the time, many, many times a day, depends more on your codebase than the language you're developing. And, for diagnostics, if you want to do some good and deep relations you can string together those natural joins and completely avoid looking up field ID's. And, as St. Jerome famously said, "Ignorance of SQL is ignorance of databases". –  Peter Turner Oct 18 '11 at 15:57

Why not just ask your teacher?

Think about this: when all your tables' PK columns are named ID, it complicates using them as foreign keys.

Column names need to be semantically significant. ID is too generic.

share|improve this answer
6  
too generic for what? the id of a table? –  jim Oct 17 '11 at 18:24
3  
@Jim of which table? id alone doesn't mean anything, especially in the context of a foreign key to another table. This has nothing to do with classes and everything to do with good basic fundamental relational database design. –  Jarrod Roberson Oct 17 '11 at 21:32
7  
To be slightly fatuous, the table which it belongs to. table.id is a perfectly acceptable way of referring to an id field. Prefixing the field name with the table name is redundant. –  Slomojo Oct 18 '11 at 1:21
1  
@Slomoj it is no more typing than including the name in the column and more explict when aliasing table names to single or double letter abbreviations in monster joins. –  Jarrod Roberson Oct 18 '11 at 3:06
3  
Of what nightmare are you referring? Suppose you have a self-referencing structure of employees with a column representing their manager. What do you call the foreign key? You can't call it EmployeeId as that is presumably your PK. The name of the FK does not have to match the PK. It should be named for what it represents to the entity in which it is contained. –  Thomas Oct 18 '11 at 17:30

There is a situation where sticking "ID" on every table isn't the best idea: the USING keyword, if it's supported. We use it often in MySQL.

For example, if you have fooTable with column fooTableId and barTable with foreign key fooTableId, then your queries can be constructed as such:

SELECT fooTableId, fooField1, barField2 FROM fooTable INNER JOIN barTable USING (fooTableId)

It not only saves typing, but is much more readable compared to the alternative:

SELECT fooTable.Id, fooField1, barField2 FROM fooTable INNER JOIN barTable ON (fooTable.Id = barTable.foTableId)
share|improve this answer

From data.stackexchange.com

Id in Posts

BOOM, question answered.
Now go tell your teacher that SO practice bad database design.

share|improve this answer
3  
My guess at the FKs, based on the names: PostTypeId -> PostTypes.Id; AcceptedAnswerId -> Answers.Id; OwnerUserId -> Users.Id. Why should a practice that is that easy be considered 'bad'? –  Sjoerd Apr 18 '12 at 0:11
1  
How exactly does this prove anything about best practices? –  GBa Apr 18 '12 at 15:52
1  
Whether something is used at stack or not does not prove if it's good or bad practice. –  Pieter B Oct 20 '12 at 14:39

There are some answers that approach what I would consider the most important reason for not using "id" as the column name for the primary key in a table: namely consistency and reduced ambiguity.

However, for me the key benefit is realized by the maintenance programmer, in particular one who was not involved with the original development. If you used the name "PersonID" for the ID in the Person table and consistently used that name as a foreign key, it is trivial to write a query against the schema to find out what tables have PersonID without having to infer that "PersonID" is the name used when it is a foreign key. Remember, right or wrong, foreign key relationships are not always enforced in all projects.

There is an edge case where one table may need to have two foreign keys to the same table, but in such cases I would put the original key name as the suffix name for the column, so a wildcard match, %PersonID, could easily find those instances as well.

Yes, much of this could be accomplished by a standard of having "id" and knowing to always use it as "tableNameID", but that requires both knowing that the practice is in place and depending on the original developers to follow through with a less intuitive standard practice.

While some people have pointed out that it does require some extra key strokes to write out the longer column names, I would posit that writing the code is only a small fraction of the active life of the program. If saving developer keystrokes was the goal, comments should never be written.

As someone who has spent many years maintaining large projects with hundreds of tables, I would strongly prefer consistent names for a key across tables.

share|improve this answer
1  
No. In your example I would probably have a CompanyOfficer or CompanyPerson table which allows a many-to-many relationship with between Company and Person with additional information about the nature of the relationship. If I were to implement it within the Company table, I would use the column names PresidentPersonID and TreasurerPersonID to preserve the *PersonID part of the name while adding the additional descriptor. inner join Person as Presidents on Company.PresidentPersonID = Presidents.PersonID –  Malachi Apr 25 '12 at 16:28

I always use 'id' as the primary column name for every table simply because it's the convention of the frameworks I use (Ruby on Rails, CakePHP), so I don't have to override it all the time.

That won't beat academic reasons for me.

share|improve this answer

ID is bad for the following reasons:

If you do a lot of reporting queries you always have to alias the columns if you want to see both. So it becomes a waste of time when you could name it properly to begin with. These complex queries are hard enough (I write queries that can be hundreds of lines long) without the added burden of doing unnecessary work.

It is subject to causing code errors. If you use a database that allows the use of the natural join (not that I think you should ever use that but when features are available somebody will use them), you will join on the wrong thing if you get a developer that uses it.

If you are copying joins to create a complex query, it is easy to forget to change the alias to the one you want and get an incorrect join. If each id is named after the table it is in, then you will usually get a syntax error. It is also easier to spot if the join ina complex query is incorrect if the pPK name and the FK name match.

share|improve this answer

I don't think it's a bad practice if it's used properly. It's common to have an auto-incrementing ID field called "ID" that you never have to touch, and use a friendlier identifier for the application. It can be a little cumbersome to write code like from tableA a inner join tableB b on a.id = b.a_id but that code can be tucked away.

As a personal preference I tend to prefix the Id with the name of the entity, but I don't see a real issue with just using Id if it's handled entirely by the database.

share|improve this answer

ID is common enough, that I don't think it would confuse anyone. You're always going to want to know the table. Putting fields names in production code without including a table/alias is a bad practice. If you're overly concerned about being able to quickly type ad hoc queries, you're on your own.

Just hope no one develops a sql database where ID is a reserved word.

CREATE TABLE CAR (ID);

Takes care of the field name, primary key, and auto increments by 1 starting with 1 all in one nice little 2 character package. Oh, and I would have called it CARS but if we're going to save on key-strokes and who really thinks a table called CAR is going to only have one?

share|improve this answer

This question has been beaten over and over again, but I thought that I too would add my opinion.

  1. I use id to mean that that is the identifier for each table, so when I join to a table and I need the primary key I automatically join to the primary key.

  2. The id field is an autoincrement, unsigned (meaning that I never have to set its value and it cannot be negative)

  3. For foreign keys, I use tablenameid (again a matter of style), but the primary key I join to is the id field of the table, so the consistency means I can always check queries easily

  4. id is short and sweet too

  5. Additional convention - use lower case for all table and column names, so no issues to be found due to case

share|improve this answer

Another thing to consider is that if the primary key name is different from the foreign key name, then it is not possible to use certain third party tools.

For example, you would be unable to load your schema into a tool like Visio and have it produce accurate ERD's.

share|improve this answer

The practice of using Id as primary key field leads to the practice where id gets added to every table. A lot of tables already have unique information that uniquely identifies a record. Use THAT as primary key and not an id field you add to each and every table. That's one of the foundations of relational databases.

And that's why using id is bad practice: id is often not information just an autoincrease.

consider the following tables:

PK id | Countryid   | Countryname
    1 |         840 | United States
    2 |         528 | the Netherlands

What's wrong with this table is that it enables the user to add another line: United States, with countrycode 840. It just broke relational integrity. Ofcourse you can enforce uniqueness on individual columns, or you could just use a primary key that's already available:

PK Countryid   | Countryname
           840 | United States
           528 | the Netherlands

That way you use the information you already have as primary key, which is at the heart of relational database design.

share|improve this answer

I find people here cover pretty much every aspect but I want to add that "id" is not and should not be read as "identifier" it's more of an "index" and surely it does not state or describe the row's identity. (I may have used wrong wording here, please correct me if I did)

It's more or less how people read the table data and how they write their code. I personally and most likely this is the most popular way I see more frequently is that coders write the full reference as table.id, even if they don't need to do union's or/and joins. For example:

SELECT cars.color, cars.model FROM cars WHERE cars.id = <some_var>

That way you can translate it to English as "Give me color and model of that car that is numbered as ." and not as "Give me color and model of that car that is identified as number ." The ID does not represent the car in any way, it's only car's index, a serial number if you will. Just like when you want to take the third element from an array.

So to sum up what I wanted to add is that it's just a matter of preference and the described way of reading SQL is the most popular.

Though, there are some cases where this is not used, such as (a far more rare example) when the ID is a string that is really describing. For example id = "RedFordMustang1970" or something similar. I really hope I could explain this at least to get the idea.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.