SQL Sql中列命名的最佳实践
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/664017/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best practices for column naming in Sql
提问by Shawn
Say I have a table called Student. Which of the following naming conventions do you prefer for the columns? You can also suggest your own.
假设我有一张名为 Student 的表。对于列，您更喜欢以下哪种命名约定？你也可以推荐你自己的。
Student
-------
StudentID
StudentName
MentorID
Student
-------
StudentID
Name
MentorID
Student
-------
ID
Name
MentorID
回答by Ray
I would go with the second one.
我会选择第二个。
Student
-------
StudentID
Name
MentorID
I like have the name of the table in the Primary key, but it doesn't need to be on every field. Also MentorID would be how I'd name a foreign key as well (assuming Mentor is the name of the table it's pointing to).
我喜欢在主键中有表的名称，但它不需要在每个字段上。MentorID 也是我命名外键的方式（假设 Mentor 是它指向的表的名称）。
This way the MentorID field in the Student table has the same name as the MentorID field in the Mentor table. Some people don't like it because it can be a bit confusing when joining tables, but I prefer to explicitly name the tables of the fields in joins anyway,
这样，Student 表中的 MentorID 字段与 Mentor 表中的 MentorID 字段具有相同的名称。有些人不喜欢它，因为在连接表时可能会有点混乱，但我更喜欢在连接中明确命名字段的表，
回答by Bj?rn
Since regular RDBMS are kind of hierarchical, a DBMS contains a database - a database contains a table - a table contains a column - a column contains a value, I don't like the iterative use of table names in the column names.
由于常规 RDBMS 是一种层次结构，因此 DBMS 包含一个数据库 - 一个数据库包含一个表 - 一个表包含一个列 - 一个列包含一个值，我不喜欢在列名中反复使用表名。
My vote goes to:
我的投票去：
Student
--------
id (pk)
name
mentor (fk) (alt. mentorId)
It's fairly easy to select correct fields, and in case of joins between tables I often rename the column names, i.e:
选择正确的字段相当容易，并且在表之间连接的情况下，我经常重命名列名，即：
SELECT s.id AS StudentID, s.name AS StudentName, m.id AS MentorId, m.name AS MentorName
FROM Studens AS s
INNER JOIN Mentors AS m ON m.id=s.mentor
回答by Andreas Petersson
since some sql formatters uppercase stuff, i go with the follwing:
由于一些 sql 格式化程序大写的东西，我用以下方法：
student
-------
id
name
mentor_id
that way i can keep word separation in the db.
这样我就可以在数据库中保持单词分离。
in OO-code i use the corresponding camel-case names
在 OO 代码中，我使用相应的驼峰式名称
mentorId, getMentorId()
导师 ID，getMentorId()
回答by Tom H
I would personally go with:
我个人会选择：
Students
--------
student_id
first_name
last_name
mentor_id
I prefer to use underscores because studies have shown that they improve readability of code immensely versus camel-back notation.
我更喜欢使用下划线，因为研究表明，与驼峰符号相比，它们极大地提高了代码的可读性。
I can also understand arguments for just using "id" rather than "student_id", so I'm not averse to that.
我也可以理解只使用“id”而不是“student_id”的论据，所以我并不反对。
回答by Dana
I prefer the last one so that a join between the tables look like:
我更喜欢最后一个，以便表之间的连接看起来像：
SELECT blah blah blah
FROM Student INNER JOIN Mentor
ON Student.MentorID = Mentor.ID
But this is nearly as subjective as "do you like camel case?" :P
但这几乎和“你喜欢骆驼壳吗？”一样主观。:P
The main thing is to be consistent. I've had to deal in the past with some databases where they could never decide on a standard. So in some tables the PK would be StudentID, others Student_ID and others ID. Or they weren't used consistently name when used as foreign keys. Oy, I'm starting to rant...
最主要的是要保持一致。过去我不得不处理一些他们永远无法决定标准的数据库。因此，在某些表中，PK 将是 StudentID，其他是 Student_ID 和其他 ID。或者它们在用作外键时没有一致使用名称。哎呀，我开始吐槽了……
回答by FerranB
I preffer the second one:
我更喜欢第二个：
Students
-------
StudentID
Name
MentorID
Where:
在哪里：
- All the foreign keys are identified with
IDon the end of columnname. - The rest of columns are named with easy to understand.
- Also use
EndDate,BeginDatefor dates.
- 所有外键都
ID在 columnname 的末尾标识。 - 其余列的名称易于理解。
- 也使用
EndDate,BeginDate表示日期。
回答by Guffa
I prefer the first one.
我更喜欢第一个。
By giving the fields a more specific name than just Idor Name, it's easier to see that you are joining correctly, and you don't have to use aliases for the fields if you select fields from more than one table:
通过为字段指定一个比Idor更具体的名称Name，更容易看出您是否正确加入，并且如果您从多个表中选择字段，则不必为字段使用别名：
select s.StudentId, s.StudentName, m.MentorId, m.MentorName
from Student s
inner join Mentor m on m.MentorId = s.MentorId
vs.
对比
select s.Id as StudentId, s.Name as StudentName, m.Id as MentorId, m.Name as MentorName
from Student s
inner join Mentor m on m.Id = s.MentorId
Also, the word Nameis a reserved keyword in some databases (for example SQL Server), so it's not always practical to use as field name.
此外，该词Name在某些数据库（例如 SQL Server）中是保留关键字，因此用作字段名称并不总是实用。
回答by JoeInthefalls
I would go with Number 1:
我会选择 1：
Avoid reserve names like "name". give fields distinctive names. I you have to repeat the table name, so be it, although I do not like seeing the table name in all the fields.
避免保留名称，如“名称”。为字段赋予独特的名称。我必须重复表名，就这样吧，虽然我不喜欢在所有字段中看到表名。
Avoid just using ID as then you have NO idea what ID is to what table. Make it unambigous ANd you then have to qualify it anyways. student_ID = mentor_ID is a WHOLE lot more readable than a.id = b.id. That is not useful, hard to read, have to then figure out what a and b is and is NOT an agile practice. Code/SQL should be easy readable w/o commenting.
避免仅使用 ID，因为您不知道什么是表的 ID。使其明确，然后无论如何您都必须对其进行限定。student_ID =mentor_ID 比 a.id = b.id 更具可读性。那没有用，很难阅读，然后必须弄清楚 a 和 b 是什么，而不是敏捷实践。代码/SQL 应该易于阅读，无需注释。
User of underscore helps with readability, camel case aside (as that is what I use in C#) I always put the PK as the first field name and the associated FK as the 2nd, 3rd, etc fields.
下划线的用户有助于提高可读性，撇开驼峰式大小写（正如我在 C# 中使用的那样），我总是将 PK 作为第一个字段名称，将关联的 FK 作为第二个、第三个等字段。
Do not end a field name with _s or _d to deliniate string or date.
不要以 _s 或 _d 结束字段名称以分隔字符串或日期。
I like things tidy and unambigous because i want to be considerate to others coming behind me that have to do maint on the DB. Too many people drag bad habits from Access into SQL. Mostly because they had no mentor to help them learn! :-)
我喜欢整洁和明确的东西，因为我想体谅身后必须维护数据库的其他人。太多人将不良习惯从 Access 拖到 SQL 中。主要是因为他们没有导师来帮助他们学习！:-)
Remember, on going maintenance is always a larger on going task than original development.
请记住，持续维护总是比原始开发更大的持续任务。
回答by achinda99
As much as I hate it, I'd go with Option 1:
尽管我很讨厌它，但我会选择选项 1：
Student
-------
StudentID
StudentName
MentorID
The reason for this is when joining with other tables with the column "Name", say Course or Degree or something, joining requires that you rename the columns to avoid ambiguous names. Dealing with long names that have table name in it is annoying, but it can save you work on the long run.
这样做的原因是当与其他带有“名称”列的表（例如课程或学位或其他内容）连接时，连接要求您重命名列以避免名称模糊。处理包含表名的长名称很烦人，但从长远来看，它可以节省您的工作。
回答by Adrian Thompson Phillips
As a side note, like the shortening of words like Company, Brother and Number to Co, Bro and No, I would recommend Identity be shortened to 'Id' instead of 'ID', as the capitalisation of the letter 'd' suggests that 'Id' is an acronym instead of an abbreviation.
作为旁注，就像将 Company、Brother 和 Number 缩写为 Co、Bro 和 No 一样，我建议将 Identity 缩写为“Id”而不是“ID”，因为字母“d”的大写表明“Id”是首字母缩写词而不是缩写词。
