MySQL naming conventions, should field name includ

问题:

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.

回答1:

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.

回答2:

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").

回答3:

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.

回答4:

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.

回答5:

Prefixing the column name with the table name is a way of guaranteeing unique column names, which makes joining easier.

But it is a tiresome practice, especially if when we have long table names. It's generally easier to just use aliases when appropriate. Besides, it doesn't help when we are self-joining.

As a data modeller I do find it hard to be consistent all the time. With ID columns I theoretically prefer to have just ID but I usually find I have tables with columns called USER_ID, ORDER_ID, etc.

There are scenarios where it can be positively beneficial to use a common column name across multiple tables. For instance, when a logical super-type/sub-type relationship has been rendered as just the child tables it is useful to retain the super-type's column on all the sub-type tables (e.g. ITEM_STATUS) instead of renaming it for each sub-type (ORDER_ITEM_STATUS, INVOICE_ITEM_STATUS, etc). This is particularly true when they are enums with a common set of values.

回答6:

For example, your database has tables which store information about Sales and Human resource departments, you could name all your tables related to Sales department as shown below:

SL_NewLeads SL_Territories SL_TerritoriesManagers

You could name all your tables related to Human resources department as shown below:

HR_Candidates HR_PremierInstitutes HR_InterviewSchedules

This kind of naming convention makes sure, all the related tables are grouped together when you list all your tables in alphabetical order. However, if your database deals with only one logical group of tables, you need not use this naming convention.

Note that, sometimes you end up vertically partitioning tables into two or more tables, though these partitions effectively represent the same entity. In this case, append a word that best identifies the partition, to the entity name

回答7:

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.

回答8:

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.

回答9:

We should define primary keys with prefix of tablename.

We should use use_id instead if id and post_id instead of just id.

Benefits:-

1) Easily Readable

2) Easily differentiate in join queries. We can minimize the use of alias in query.

user table : user_id(PK)

post table : post_id(PK) user_id(FK) here user table PK and post table FK are same

As per documentation,

3) This way we can get benefit of NATURAL JOIN and JOIN with USING

Natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions (prior to 5.0.12) must be rewritten to comply with the standard.

These changes have five main aspects:

1) The way that MySQL determines the result columns of NATURAL or USING join operations (and thus the result of the entire FROM clause).

2) Expansion of SELECT * and SELECT tbl_name.* into a list of selected columns.

3) Resolution of column names in NATURAL or USING joins.

4) Transformation of NATURAL or USING joins into JOIN ... ON.

5) Resolution of column names in the ON condition of a JOIN ... ON.

Examples:-

SELECT * FROM user NATURAL LEFT JOIN post;
SELECT * FROM user NATURAL JOIN post;
SELECT * FROM user JOIN post USING (user_id);