Sql naming best practice

Question

I'm not entirely sure if there's a standard in the industry or otherwise, so I'm asking here.

I'm naming a Users table, and I'm not entirely sure about how to name the members.

user_id is an obvious one, but I wonder if I should prefix all other fields with "user_" or not.

user_name user_age

or just name and age, etc...

Answer 1

I agree with the other answers that suggest against prefixing the attributes with your table names.

However, I support the idea of using matching names for the foreign keys and the primary key they reference¹, and to do this you'd normally have to prefix the id attributes in the dependent table.

Something which is not very well known is that SQL supports a concise joining syntax using the USING keyword:

CREATE TABLE users (user_id int, first_name varchar(50), last_name varchar(50));
CREATE TABLE sales (sale_id int, purchase_date datetime, user_id int);

Then the following query:

SELECT s.*, u.last_name FROM sales s JOIN users u USING (user_id);

is equivalent to the more verbose and popular joining syntax:

SELECT s.*, u.last_name FROM sales s JOIN users u ON (u.user_id = s.user_id);

---

¹ This is not always possible. A typical example is a user_id field in a users table, and reported_by and assigned_to fields in the referencing table that both reference the users table. Using a user_id field in such situations is both ambiguous, and not possible for one of the fields.