每当我设计数据库时,我总是想知道是否有一种最好的方法来命名数据库中的项目。我经常问自己以下问题:
表名应该是复数吗? 列名应该是单数吗? 我应该为表或列添加前缀吗? 我应该在命名项目时使用大小写吗?
是否有推荐的指导原则来命名数据库中的项?
每当我设计数据库时,我总是想知道是否有一种最好的方法来命名数据库中的项目。我经常问自己以下问题:
表名应该是复数吗? 列名应该是单数吗? 我应该为表或列添加前缀吗? 我应该在命名项目时使用大小写吗?
是否有推荐的指导原则来命名数据库中的项?
当前回答
在我看来:
表名应该是复数形式。 列名应该是单数。 不。 对于表名和列名,可以选择CamelCase(我的首选)或underscore_separated。
然而,就像上面提到的,任何约定都比没有约定好。无论您选择如何做,都要记录它,以便将来的修改遵循相同的约定。
其他回答
请参阅ISO 11179-5:命名和识别原则 你可以在这里获得:http://metadata-standards.org/11179/#11179-5
我之前写过一篇博文:ISO-11179命名约定
--Example SQL
CREATE TABLE D001_Students
(
StudentID INTEGER CONSTRAINT nnD001_STID NOT NULL,
ChristianName NVARCHAR(255) CONSTRAINT nnD001_CHNA NOT NULL,
Surname NVARCHAR(255) CONSTRAINT nnD001_SURN NOT NULL,
CONSTRAINT pkD001 PRIMARY KEY(StudentID)
);
CREATE INDEX idxD001_STID on D001_Students;
CREATE TABLE D002_Classes
(
ClassID INTEGER CONSTRAINT nnD002_CLID NOT NULL,
StudentID INTEGER CONSTRAINT nnD002_STID NOT NULL,
ClassName NVARCHAR(255) CONSTRAINT nnD002_CLNA NOT NULL,
CONSTRAINT pkD001 PRIMARY KEY(ClassID, StudentID),
CONSTRAINT fkD001_STID FOREIGN KEY(StudentID)
REFERENCES D001_Students(StudentID)
);
CREATE INDEX idxD002_CLID on D002_Classes;
CREATE VIEW V001_StudentClasses
(
SELECT
D001.ChristianName,
D001.Surname,
D002.ClassName
FROM
D001_Students D001
INNER JOIN
D002_Classes D002
ON
D001.StudentID = D002.StudentID
);
这些是我学到的惯例,但是您应该适应您的开发软管使用的任何东西。
复数。它是实体的集合。 是的。属性是一个实体的单一属性的表示。 是的,前缀表名允许轻松跟踪所有约束索引和表别名的命名。 表和列名用大小写,索引和约束用前缀+ ALL大写。
表名:它应该是单数,因为它是一个表示真实世界对象的单数实体,而不是表示对象的单数实体。
列名:它应该是单数,只有这样它才表示它将持有一个原子值,并将确认归一化理论。然而,如果有n个相同类型的属性,那么它们应该以1,2,…作为后缀。n,等等。
表/列前缀:这是一个巨大的主题,将在后面讨论。
外壳:应该是驼色的
我的朋友Patrick Karcher,我请求你不要写任何可能冒犯别人的东西,就像你写的那样,“此外,外键必须在不同的表中一致命名。如果有人不这样做,殴打他应该是合法的。”我从来没有犯过这样的错误,我的朋友帕特里克,但我写一般。如果他们一起打算为此揍你呢?:)
我总是听到这样的争论,即表格是否多元化完全是个人品味的问题,没有最佳实践。我不相信这是真的,尤其是作为一个程序员而不是DBA。据我所知,除了“这对我来说很有意义,因为它是对象的集合”之外,没有其他合理的理由将表名改为复数形式,而使用单数表名在代码中有合理的好处。例如:
It avoids bugs and mistakes caused by plural ambiguities. Programmers aren't exactly known for their spelling expertise, and pluralizing some words are confusing. For example, does the plural word end in 'es' or just 's'? Is it persons or people? When you work on a project with large teams, this can become an issue. For example, an instance where a team member uses the incorrect method to pluralize a table he creates. By the time I interact with this table, it is used all over in code I don't have access to or would take too long to fix. The result is I have to remember to spell the table wrong every time I use it. Something very similar to this happened to me. The easier you can make it for every member of the team to consistently and easily use the exact, correct table names without errors or having to look up table names all the time, the better. The singular version is much easier to handle in a team environment. If you use the singular version of a table name AND prefix the primary key with the table name, you now have the advantage of easily determining a table name from a primary key or vice versa via code alone. You can be given a variable with a table name in it, concatenate "Id" to the end, and you now have the primary key of the table via code, without having to do an additional query. Or you can cut off "Id" from the end of a primary key to determine a table name via code. If you use "id" without a table name for the primary key, then you cannot via code determine the table name from the primary key. In addition, most people who pluralize table names and prefix PK columns with the table name use the singular version of the table name in the PK (for example statuses and status_id), making it impossible to do this at all. If you make table names singular, you can have them match the class names they represent. Once again, this can simplify code and allow you to do really neat things, like instantiating a class by having nothing but the table name. It also just makes your code more consistent, which leads to... If you make the table name singular, it makes your naming scheme consistent, organized, and easy to maintain in every location. You know that in every instance in your code, whether it's in a column name, as a class name, or as the table name, it's the same exact name. This allows you to do global searches to see everywhere that data is used. When you pluralize a table name, there will be cases where you will use the singular version of that table name (the class it turns into, in the primary key). It just makes sense to not have some instances where your data is referred to as plural and some instances singular.
总而言之,如果你将表名改为复数,那么你就失去了让你的代码更聪明、更容易处理的所有优势。甚至在某些情况下,您必须使用查找表/数组来将表名转换为本可以避免的对象或本地代码名。虽然一开始可能感觉有点奇怪,但单数表名比复数表名具有显著优势,我相信这是最佳实践。
我也支持ISO/IEC 11179风格的命名约定,指出它们是指导方针而不是规定。
参见维基百科上的数据元素名称:
表是实体的集合,并遵循集合命名准则。理想情况下,使用一个集合名称。、人员。复数形式也是正确的:Employees。不正确的名称包括:Employee、tblEmployee和EmployeeTable。”
与往常一样,规则也有例外,例如,一个总是只有一行的表可能用一个奇异的名字更好,例如配置表。一致性是最重要的:检查你的购物是否有惯例,如果有,就遵守它;如果你不喜欢它,那就做一个商业案例来改变它,而不是做一个独行侠。