Is it better to specify the primary key column id or * _id?

Question

I have been using Rails for several years, and I'm used to the legend of the id primary column column. But I came across a lot of examples in SQL books that call the primary key column something like employee_id for the employees table or feed_id for the feeds table.

One of the advantages of the 2nd system is that you can use USING() more to get more compressed SQL queries:

 select feeds.title, items.title from items inner join feeds USING(feed_id); 

Unlike

 select feeds.title, items.title from items inner join feeds on feeds.id = items.feed_id; 

Which naming convention is better? What do experienced database administrators prefer?

Also, is it better to pluralize the table name?

Answer 1

Tablename_Id is my strong preference. When you join Fks, you know exactly what to join, and don’t make mistakes when you join the ID in table a, when you had the sign below, is an example of how easy it is, especially if you copy the sentence on from somewhere else

 FROM tablea a JOIN tableb b ON a.ID = b.tableaid JOIN tablec c ON a.ID = c.tablebid 

In the above case, you really wanted to join B.Id, but forgot to change it from the moment of copying. It will work and give you a result that is incorrect. If you use table_id instead, the query cannot check the syntax.

Another problem with using Id is when you run complex reports. Since repport requests must have fields with separate names, you can end up wasting time creating a bunch of aliases that you don't need if you named the identifier using a tab name.

Now people who use ORM do not write a lot of SQl, but what they write and what report authors write is usually complicated, complicated. You need to design a database to simplify these tasks than simple queries.

Using an identifier as a name for an identification field is considered SQl anti-spam. http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557/ref=sr_1_1?s=books&ie=UTF8& QID = 1308929815 & cf = 1-1

Answer 2

I always use a detailed form (that is, "employee_id", not "id"), since it is more descriptive. If you join multiple tables and both have an id column, you will have to assign an alias to id if you need to select both identifiers. In addition, as you mentioned, you get the advantage of the USING position. In the grand scheme of things, this is not a huge factor, anyway, but a more complex form gives you advantages.

Answer 3

Both parameters are valid, but purists will use the usage identifier because its name is listed in the table.

I use table_id because I find it more descriptive and simplifies debugging. It is more practical.

Re: Tabular names. Another hotly debated topic among database nerds, but I say Singular.

Answer 4

This is user preference, but I always name the main keys of my Id tables. I always refer to references of this Identifier in other tables as [SingularEntityName] [Id], for example.

 Credentials Id Password Users Id Name CredentialId Descriptions Id UserId 

Keeps my links clean. However, just be consistent in your name, and it really doesn't matter how you set up your circuits.

Answer 5

To reopen the can of worms, I bet that those who choose tablename_id are older and more experienced programmers. Those who use only id are younger.

Why? because you learn redundancy and constancy is not always bad. the only thing I would like to add to answer # 1, using "_" helps to facilitate the selection of a variable in the code, in the table, etc. .... I do the same for foreign keys. TableName_FK Some will argue about this, but it works for me, and it is obvious what it is.

I had to work a lot on other code for many years. Consistency is critical, obfuscation is useless and meaningful variable names are very useful. There are those who claim that verbosity makes code harder to read. I do not think this argument flies in the modern world of objects. That.derive.from.some.microsoft.class.twenty.layers.deep.that.you.have.to.fully.reference.

BTW - as many have said, this is your choice. Those people who spend time arguing about coding syntax do not have enough work. Learn to be flexible and use the standards of the workplace where you work. If you are fortunate enough to set your own standards, then you have one. The fact that you are wondering is wonderful. But choose one and then be consistent (until you change assignments or decide that you have a paradigm shift, which means you want to change your style.)

You can often choose in which era someone began to learn to code their personal preferences and styles. The guys who write very hard, minmal, hard to read code returned when the memory was very limited (DOS) and probably wrote a lot of assembler, those using Hungarian started working with the Win SDK, etc.

This discussion has been developing for decades. The older I get, the more I document my code, the more meaningful I make my variable names, etc. .... because in a week I will forget what I wrote and I need roadmaps to figure it out. Not so much that I forgot, although this part of the equation, but even more so because I write code in many different projects.

Answer 6

It is completely your choice. But personally, I prefer the latter, since I do not need to look for table names in my code when I come across an identifier. I think tablename_id is better.

Answer 7

Another advantage of naming primary keys that are unique to this table is that it makes it easier to use the naming convention when accessing these keys in different tables, which indicates the corresponding key.

For example, suppose everything in your alpha table starts with alpha_, so you have alpha_id as the primary key. In your beta table — where beta_ will start — you will use beta_alpha_id to refer to keys in the alpha table in this table.