MySQL naming conventions, should field name includ

Question

A friend told me that I should include the table name in the field name of the same table, and I'm wondering why? And should it be like this? Example:

(Table) Users  
(Fields) user_id, username, password, last_login_time

I see that the prefix 'user_' is meaningless since I know it's already for a user. But I'd like to hear from you too. note: I'm programming in php, mysql.

Answer 1

Personally I don't add table names for field names in the main table but when using it as a foreign field in another table, I will prefix it with the name of the source table. e.g. The id field on the users table will be called id, but on the comments table it, where comments are linked to the user who posted them, it will be user_id.

This I picked up from CakePHP's naming scheme and I think it's pretty neat.

Answer 2

Sounds like the conclusion is: If the field name is unique across tables - prefix with table name. If the field name has the potential to be duplicated in other tables, name it unique.

I found field names such as "img, address, phone, year" since different tables may include different images, addresses, phone numbers, and years.

Answer 3

I see no reason to include the table name, it's superfluous. In the queries you can refer to the fields as <table name>.<field name> anyway (eg. "user.id").

Answer 4

I agree with you. The only place I am tempted to put the table name or a shortened form of it is on primary and foreign keys or if the "natural" name is a keyword.

Users: id or user_id, username, password, last_login_time
Post: id or post_id, user_id, post_date, content

I generally use 'id' as the primary key field name but in this case I think user_id and post_id are perfectly OK too. Note that the post date was called 'post_date" because 'date' is a keyword.

At least that's my convention. Your mileage may vary.

Answer 5

Actually, there is a reason for that kind of naming, especially when it comes to fields, you're likely to join on. In MySQL at least, you can use the USING keyword instead of ON, then users u JOIN posts p ON p.user_id = u.id becomes users u JOIN posts p USING(user_id) which is cleaner IMO.

Regarding other types of fields, you may benefit when selecting *, because you wouldn't have to specify the list of the fields you need and stay sure of which field comes from which table. But generally the usage SELECT * is discouraged on performance and mainenance grounds, so I consider prefixing such fields with table name a bad practice, although it may differ from application to application.

Answer 6

With generic fields like 'id' and 'name', it's good to put the table name in.

The reason is it can be confusing when writing joins across multiple tables.

It's personal preference, really, but that is the reasoning behind it (and I always do it this way).

Whatever method you choose, make sure it is consistent within the project.