Why is naming a table's Primary Key column "Id" considered bad practice?
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?
Well, it is not a description actually and Id means "identity" which is very self explanatory. That is my opinion.
For the record, even if it is a bad practice, it is a common practice. Professors live mostly in a world of theory.
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.
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.
Perhaps you should first ask "Is the *really* a bad practice"?
It's akin to naming a string variable like this: `string stringVariable = '';`
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.
Naming your PK Table_PK or Table_ID is extremely helpful when you're doing joins with FKs and using join tables, as X table's PK is ALWAYS table_ID, none of this table.id=jointable.table_id stuff
Well if you do like table.ID you're in good company. see http://data.stackexchange.com
Anyone can call something bad practice. Something has to have a reason to be a bad practice. Next time, (politely) challenge the teacher to explain why it's bad practice.
I'm amazed this question has been up voted so much. This is a question of personal style that has turned into a religious war. In the real world, who cares? Follow the style your team uses.
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.
Sometimes the choice a teacher has due to time constraints and the scope of a course are: Informing about, but not explaining X vs Not informing about X. Which option is chosen depends on the course and the scope of a course. If for example a teacher is given the order to teach Python, but not object oriented programming, some things are just left at "use the dot to access functions that belong to the variable", rather than explaining that some things are called methods, some functions and that some things can be instances etc.
Bad practice? Depends on context. Personally I find it better to follow a convention of NounId or TablenameId, but I do a lot of code generation, binding by convention, meta-data based logic and so forth wherein being able to infer the table from the Id's name can be very convenient. Also, when joining / creating views it is convenient to not have to ALIAS "Id" all over the place to avoid naming collisions in the flattened structure. But that aside I would say that this is much more of a PREFERENCE than a PRACTICE, good bad or otherwise.
Id is common practice if you are a .net developer using Entity Framework. For years now EF 4.x and now 5.0 use Id here is one example http://blogs.msdn.com/b/efdesign/archive/2010/06/01/conventions-for-code-first.aspx
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
carswith the abbreviation
car? 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.
@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.
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.
@Chad, completely true. The thing that drives me crazy about that one is that I do it without thinking and then hate myself later :)
I do it all the time too...it pisses me off because I don't notice until it's too late to change without great difficulty.
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, 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.
@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)
And the majority of students unfortunately will go on ignoring best practices and have to be "retrained" when they are employed...IT needs an apprenticeship path like tradespeople have.
Why did you pick multiple nested correlated sub queries as a canonical bad practice? I don't really see that technique as overused. I would have picked concatenating values to SQL strings (i.e. SQL Injection).
@ConradFrix, simply because it was a problem I had to deal with recently--no other reason than that.
I always find myself lost in corner cases like this. +1 good advise. Sorry I took away your perfect 80. :P
A side note: "cars" should not be abbreviated as "car", but "crs". That doesnt look much better, and I totally agree that using 3 letter abbreviations are a bad thing.
Naming every PK `Id` by itself isn't a bad practice, but it leaves you having to write out tedious `ON` clauses instead of using `NATURAL JOIN` or `JOIN USING`. I wonder if the teacher didn't mean that adding surrogate `Id` columns everywhere instead of actually thinking about your model -- a la Rails -- is the bad practice.
A big reason to use "Id" over "tablenameId" is many ORMs encourage that use (eg Dapper Contrib) & require less configuration if "Id" is used. Under those circumstances "Id" could be argued as *best practice*.
Isn't the examples in the wrong order. You have an example using shorter aliases and the text then goes on to say that you can use short aliases followed by an example that uses the full names. At least to me that's somewhat confusing.
@skyking That isn't what the text of the answer says at all. > "[T]his can easily be mitigated by **not** using tiny aliases for your table names:"
@riwalk OK, I first thought that the problem that was to be mitigated was repeating long names. Now I see that you refer to a problem that is pointed out by Chad which doesn't seem to be found anywhere. The order is correct right, but I'm still confused about what problem you're avoiding with the second query then.