每当我设计数据库时,我总是想知道是否有一种最好的方法来命名数据库中的项目。我经常问自己以下问题:
表名应该是复数吗? 列名应该是单数吗? 我应该为表或列添加前缀吗? 我应该在命名项目时使用大小写吗?
是否有推荐的指导原则来命名数据库中的项?
每当我设计数据库时,我总是想知道是否有一种最好的方法来命名数据库中的项目。我经常问自己以下问题:
表名应该是复数吗? 列名应该是单数吗? 我应该为表或列添加前缀吗? 我应该在命名项目时使用大小写吗?
是否有推荐的指导原则来命名数据库中的项?
当前回答
在我看来:
表名应该是复数形式。 列名应该是单数。 不。 对于表名和列名,可以选择CamelCase(我的首选)或underscore_separated。
然而,就像上面提到的,任何约定都比没有约定好。无论您选择如何做,都要记录它,以便将来的修改遵循相同的约定。
其他回答
虽然很晚了,但我仍然想对列前缀发表我的意见
对于使用table_column(或tableColumn)列命名标准,似乎有两个主要的论据,都是基于列名本身在整个数据库中是唯一的这一事实:
1)你不需要一直在你的查询中指定表名和/或列别名
2)你可以很容易地在整个代码中搜索列名
我认为这两种观点都有缺陷。不使用前缀解决这两个问题很简单。以下是我的建议:
在SQL中始终使用表名。例如,总是用table。列而不是列。
它显然解决了2)你现在只需要搜索表。而不是table_column。
But I can hear you scream, how does it solve 1)? It was exactly about avoiding this. Yes, it was, but the solution was horribly flawed. Why? Well, the prefix solution boils down to: To avoid having to specify table.column when there's ambiguity, you name all your columns table_column! But this means you will from now on ALWAYS have to write the column name every time you specify a column. But if you have to do that anyways, what's the benefit over always explicitly writing table.column? Exactly, there is no benefit, it's the exact same number of characters to type.
编辑:是的,我知道用前缀命名列可以强制使用正确的用法,而我的方法依赖于程序员
这里有一个链接,提供了一些选择。我正在寻找一个简单的规范,我可以遵循,而不是依赖于一个部分定义的规范。
http://justinsomnia.org/writings/naming_conventions.html
我们的偏好:
Should table names be plural? Never. The arguments for it being a collection make sense, but you never know what the table is going to contain (0,1 or many items). Plural rules make the naming unnecessarily complicated. 1 House, 2 houses, mouse vs mice, person vs people, and we haven't even looked at any other languages. Update person set property = 'value' acts on each person in the table. Select * from person where person.name = 'Greg' returns a collection/rowset of person rows. Should column names be singular? Usually, yes, except where you are breaking normalisation rules. Should I prefix tables or columns? Mostly a platform preference. We prefer to prefix columns with the table name. We don't prefix tables, but we do prefix views (v_) and stored_procedures (sp_ or f_ (function)). That helps people who want to try to upday v_person.age which is actually a calculated field in a view (which can't be UPDATEd anyway). It is also a great way to avoid keyword collision (delivery.from breaks, but delivery_from does not). It does make the code more verbose, but often aids in readability. bob = new person() bob.person_name = 'Bob' bob.person_dob = '1958-12-21' ... is very readable and explicit. This can get out of hand though: customer.customer_customer_type_id indicates a relationship between customer and the customer_type table, indicates the primary key on the customer_type table (customer_type_id) and if you ever see 'customer_customer_type_id' whilst debugging a query, you know instantly where it is from (customer table). or where you have a M-M relationship between customer_type and customer_category (only certain types are available to certain categories) customer_category_customer_type_id ... is a little (!) on the long side. Should I use any case in naming items? Yes - lower case :), with underscores. These are very readable and cross platform. Together with 3 above it also makes sense. Most of these are preferences though. - As long as you are consistent, it should be predictable for anyone that has to read it.
--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大写。
我建议你看看微软的SQL Server样本数据库: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
AdventureWorks示例使用了非常清晰和一致的命名约定,它使用模式名组织数据库对象。
表的单数名称 列的单数名称 表前缀的架构名称(例如:SchemeName.TableName) 帕斯卡壳(又称上驼峰壳)