Field naming convention and SQL statements

Question

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

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

The two tables have both "id" and "name". In a SQL query, it will be ambiguous for two if the table names are not prefixed. Two options:

• use table name as field prefix in SQL 'where' clause
• Use prefix field names in tables so that the prefix is ​​not used in the where clause.

Which one is better?

Answer 1

For queries that are not ad-hoc, you should always prefix each field with either the table name or the table alias, even if the field name is not ambiguous. This prevents the query from being interrupted later if someone adds a new column to one of the tables, which introduces ambiguity.

So that "id" and "name" are unique. But I still recommend calling the primary key something more specific than "id". In your example, I would use student_id and teacher_id . This helps prevent connection errors. In any case, you will need more specific names when starting tables with more than one unique key or multi-part keys.

It's worth thinking about it, but ultimately consistency may be a more important factor. I can handle 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 . This is the worst of both worlds.

Answer 2

No doubt go to option 1. This is a valid sql in any database and is considered the correct and most readable format. It’s a good habit to assign a table name to a column and is very necessary when connecting. The only exception to this that I most often saw was the id column prefix with the table name, but I didn't do it anyway. If you go with option 2, an experienced database administrator will probably point and laugh at you.
For further evidence, see No. 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 can make your life a lot easier if you learn to use alias for the table name. It is as simple as using SomeTableNameThatsWayTooLong as long_table in your query, for example:

 SELECT LT.Id FROM SomeTableNameThatsWayTooLong AS LT 

Answer 3

I would use aliases, not table names.

You can assign alias to a table in a query that is shorter than the table name. This makes the request more readable. Example:

 SELECT t.name AS teacher_name, s.name AS student_name FROM teacher AS t INNER JOIN student AS s ON s.id=t.s_id; 

Of course, you can use the table name if you are not using aliases, and that would be preferable to your option 2.

Answer 4

If it is not too long, I prefer the prefix in the table itself, for example. teacher.teacher_id, student.student_name. This way you are always sure which name or identifier you are saying, even if you want a table name prefix.