The correct way to write "ID" in SQL columns

Question

My first question is here. I really like the site :)

I have an SQL table called Product. In this table, I want to make a column for the product identifier, and I really want to know the correct way to write the identifier, because I make many identifier columns in the long run.

So is it: "ID" or "Id"?

And also the foreign key in another table, is it called ProductID or ProductId?

Answer 1

Personally, I would have ProductID, ProductName, etc. in the Product table and for FK too, to avoid the ID and Name columns everywhere

Just be consistent

Answer 2

Capitalization in SQL is largely dependent on the coding style. Consistency is the most important aspect. However, “ProductId” looks a bit like “Productld” (which is for lowercase L before “d”) for me, so I would prefer “ProductID” (or “productID” or “product_id”).

As for the prefix of column names with table names, this is too much in my book. Products.id enough; Products.productID is redundant.

Answer 3

There is no “right” way to do this. Just be consistent. My personal preference is to use ProductId in both tables. If you use an “ID” for all of your tables and then join another table, you will most likely end up aliasing them to distinguish between the two ID fields.

Answer 4

As you prefer.

Most (many? Some of them that I know?) Apparently used lowercase frameworks for the primary key and "primarykeytablename_id" for the foreign key, at least for the default naming convention. I myself do not like this agreement, because I like that the key has the same name on both sides of the relationship.

My preference is EntityNameID in both places (e.g. ProductID, OrderID, OrderDtlID, etc.).

Answer 5

I probably should never have taken this class of psychology, but due to the fact that there is the word "id", I always use the abbreviation for the identifier, so I use "ID". I experimented using both ID and ProductID for the primary key name. Since I use LINQ and just map the class in the designer, I decided to name the columns the way I want them in my code. Since I prefer to have product.ID than product.ProductID , I use a shorter name. For foreign keys, I use the table / column format (without a separator), so the foreign key will become ProductID. This is not a problem for me in my code, because I almost always use the displayed object, for example, cart.Product , and not the key itself, cart.ProductID .

EDIT : Note. I assume that the purpose of the .NET platform is using (mainly) naming conventions from .NET. If I were doing Rails development, they would probably be lowercase, and I would use underscores as separators.

Answer 6

Well, "Id" is not suitable for "Identification", so "should" be "Id".

On the other hand, he pronounced "ID" rather than "id", so "ID" is also acceptable.

I would prefer "ProductId" rather than just "Id" for the primary key of the table, so that both sides of the foreign key relationship are the same.

Answer 7

I tend to have no "id" or "ID".

I always go to table - productName

pkProductName Product Name fkProductCode

and table - ProductCode pkProductCode ProductCode

therefore, even when you use SQL or code, relationships are explicit and meaningful.