Table field naming convention and SQL statements

Question

I have a practical question regarding naming table fields in a database. For example, I have two tables:

student (id int; name varchar(30))
teacher (id int, s_id int; name varchar(30))

There are both 'id' and "name" in two tables. In SQL statement, it will be ambiguous for the two if no table names are prefixed. Two options:

1. use Table name as prefix of a field in SQL 'where' clause
2. use prefixed field names in tables so that no prefix will be used in 'where' clause.

Which one is better?

Answer 1

Without a doubt, go with option 1. This is valid sql in any type of database and considered the proper and most readable format. It's good habit to prefix the table name to a column, and very necessary when doing a join. The only exception to this I've most often seen is prefixing the id column with the table name, but I still wouldn't do that.
If you go with option 2, seasoned DBA's will probably point and laugh at you.
For further proof, see #2 here: https://www.periscopedata.com/blog/better-sql-schema.html
And here. Rule 1b - http://www.isbe.net/ILDS/pdf/SQL_server_standards.pdf

As TT mentions, you'll make your life much easier if you learn how to use an alias for the table name. It's as simple as using SomeTableNameThatsWayTooLong as long_table in your query, such as:

SELECT LT.Id FROM SomeTableNameThatsWayTooLong AS LT

Answer 2

For queries that aren't ad-hoc, you should always prefix every field with either the table name or table alias, even if the field name isn't ambiguous. This prevents the query from breaking later if someone adds a new column to one of the tables that introduces ambiguity.

So that would make "id" and "name" unambiguous. But I still recommend naming the primary key with something more specific than "id". In your example, I would use student_id and teacher_id. This helps prevent mistakes in joins. You will need more specific names anyway when you run into tables with more than one unique key, or multi-part keys.

It's worth thinking these things through, but in the end consistency may be the more important factor. I can deal with tables built around id instead of student_id, but I'm currently working with an inconsistent schema that uses all of the following: id, sid, systemid and specific names like taskid. That's the worst of both worlds.