我知道这有点晚了，这个问题已经得到了很好的回答，但我想就#3关于列名前缀的问题提出我的看法。

所有列都应该使用一个对定义它们的表唯一的前缀命名。

例如，给定表“customer”和“address”，让我们分别使用前缀“cust”和“addr”。"customer"中会有"cust_id"， "cust_name"等。“address”将包含“addr_id”，“addr_cust_id”(FK返回给客户)，“addr_street”等。

当我第一次看到这个标准时，我坚决反对它;我讨厌这个主意。我无法忍受所有额外的输入和冗余。现在我已经有了足够的经验，我再也不会回去了。

这样做的结果是数据库模式中的所有列都是唯一的。这有一个主要的好处，它压倒了所有反对它的论点(当然，在我看来):

您可以搜索整个代码库，并可靠地找到涉及特定列的每一行代码。

The benefit from #1 is incredibly huge. I can deprecate a column and know exactly what files need to be updated before the column can safely be removed from the schema. I can change the meaning of a column and know exactly what code needs to be refactored. Or I can simply tell if data from a column is even being used in a particular portion of the system. I can't count the number of times this has turned a potentially huge project into a simple one, nor the amount of hours we've saved in development work.

另一个相对较小的好处是，当你进行自连接时，你只需要使用表别名:

SELECT cust_id, cust_name, addr_street, addr_city, addr_state
    FROM customer
        INNER JOIN address ON addr_cust_id = cust_id
    WHERE cust_name LIKE 'J%';

我们的偏好:

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.

在我看来:

表名应该是复数形式。 列名应该是单数。 不。 对于表名和列名，可以选择CamelCase(我的首选)或underscore_separated。

然而，就像上面提到的，任何约定都比没有约定好。无论您选择如何做，都要记录它，以便将来的修改遵循相同的约定。

我总是听到这样的争论，即表格是否多元化完全是个人品味的问题，没有最佳实践。我不相信这是真的，尤其是作为一个程序员而不是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.

总而言之，如果你将表名改为复数，那么你就失去了让你的代码更聪明、更容易处理的所有优势。甚至在某些情况下，您必须使用查找表/数组来将表名转换为本可以避免的对象或本地代码名。虽然一开始可能感觉有点奇怪，但单数表名比复数表名具有显著优势，我相信这是最佳实践。

不。表应该以它所代表的实体命名。 Person，而不是persons是指记录所代表的人。 同样的事情。列FirstName真的不应该被称为FirstNames。这完全取决于你想用列表示什么。 不。 是的。为清晰起见。如果你需要像“FirstName”这样的列，大小写会让它更容易阅读。

好的。这是我的0.02美元

--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大写。