Hacker News Reader

Comment by Achshar | Hacker News Reader

https://github.com/jarulraj/sqlcheck/blob/master/docs/logica...

Strongly disagree.

Table columns should always be prefixed with table name. So users.user_id is one of the most hated patters for me. user.id looks so much cleaner. No one in their right mind should ever write the column name by itself. Especially with queries that use more than one tables.

View on HN

It's pretty annoying to do joins and have to alias all your columns because their names are ambiguous. Also in my experience most people use very terse table aliases so it's not always obvious what "u.ID" "uc.ID" "puc.ID" etc. are referring to.

You don't need to alias "all" of your columns, just about 1 out of 10. And I don't understand the complaint about terse table aliases being non-obvious. The answer key is right below the select clause.

This doesn't allow for finer semantics. Just "u.id" tells us what the thing is, but not its role in the schema.

I don't think it's very unusual to do this. The most obvious example is where there are multiple references to the same entity. For example, an Order table might contain two references to a user - one for the user who placed the order, and another for the person who approved the order.

But it's more general than this. Just saying that it's the user that's associated with the thing is frequently not enough to convey understanding, and naming with the role that the relationship is describing is helpful.

I also prefer using id instead of user_id or company_id. It does mean you'll have to escape it (in MySQL at least) if you are writing pure SQL.

Take the following example:

           SELECT s.`id`,
                  s.label,
                  s.`group`,
                  g.name AS group_name,
                  s.tags,
                  s.ssh_username,
                  s.ssh_port
             FROM servers s
  LEFT OUTER JOIN groups g
               ON s.group = g.`id`
         ORDER BY s.label ASC

In an unrelated note, how does everybody else format their SQL? The above format is what I typically use.

https://news.ycombinator.com/item?id=12861805

I agree with you in general but I avoid table names or aliases when the query uses only one table because that adds no clarity. Also, table names are often much longer than "user" so aliases can be useful. I do agree that aliases like a, b, c should be avoided and if an alias is required it should be descriptive.

On my team we have a lot of common table aliases which are used by convention. We understand what they mean and it gets the point across without using 20 characters.

Thanks for sharing your perspective. I will add a new check to ensure that table columns are always be prefixed with table name. However, I still think that it is worthwhile to not use a generic primary key name (at least in the common case).

Reply on news.ycombinator.com