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

我的观点是:

1)不，表名应该是单数。

虽然对于简单的选择(select * from Orders)似乎有意义，但对于OO等效(Orders x = new Orders)则没有意义。

数据库中的表实际上是该实体的集合，当你使用set-logic时，它更有意义:

select Orders.*
from Orders inner join Products
    on Orders.Key = Products.Key

最后一行，连接的实际逻辑，看起来与复数表名混淆。

我不确定是否总是使用别名(如Matt建议的那样)可以消除这种情况。

2)它们应该是单数，因为它们只拥有一种属性

3)如果列名有歧义(如上所述，它们都有一个名为[Key]的列)，表名(或其别名)永远不能很好地区分它们。您希望查询能够快速键入，并且简单-前缀会增加不必要的复杂性。

4)无论你想要什么，我都推荐CapitalCase

我不认为有任何一套绝对的指导方针。

只要你在应用程序或数据库中选择的是一致的，我不认为这真的很重要。

这里的回答有点晚，但简而言之:

复数表名:我的偏好是复数 单个列名:是的 前缀表或列:

表:*通常*没有前缀是最好的。 列:没有。

在命名项时使用任何大小写:表和列都使用PascalCase。

细化:

(1)你必须做什么。很少有事情是你每次都必须以某种方式去做的，但还是有一些。

Name your primary keys using "[singularOfTableName]ID" format. That is, whether your table name is Customer or Customers, the primary key should be CustomerID. Further, foreign keys must be named consistently in different tables. It should be legal to beat up someone who does not do this. I would submit that while defined foreign key constraints are often important, consistent foreign key naming is always important You database must have internal conventions. Even though in later sections you'll see me being very flexible, within a database naming must be very consistent . Whether your table for customers is called Customers or Customer is less important than that you do it the same way throughout the same database. And you can flip a coin to determine how to use underscores, but then you must keep using them the same way. If you don't do this, you are a bad person who should have low self-esteem.

你可能应该做的事。

Fields representing the same kind of data on different tables should be named the same. Don't have Zip on one table and ZipCode on another. To separate words in your table or column names, use PascalCasing. Using camelCasing would not be intrinsically problematic, but that's not the convention and it would look funny. I'll address underscores in a moment. (You may not use ALLCAPS as in the olden days. OBNOXIOUSTABLE.ANNOYING_COLUMN was okay in DB2 20 years ago, but not now.) Don't artifically shorten or abbreviate words. It is better for a name to be long and clear than short and confusing. Ultra-short names is a holdover from darker, more savage times. Cus_AddRef. What on earth is that? Custodial Addressee Reference? Customer Additional Refund? Custom Address Referral?

(3)你应该考虑什么。

I really think you should have plural names for tables; some think singular. Read the arguments elsewhere. Column names should be singular however. Even if you use plural table names, tables that represent combinations of other tables might be in the singular. For example, if you have a Promotions and an Items table, a table representing an item being a part of a promotion could be Promotions_Items, but it could also legitimately be Promotion_Items I think (reflecting the one-to-many relationship). Use underscores consistently and for a particular purpose. Just general tables names should be clear enough with PascalCasing; you don't need underscores to separate words. Save underscores either (a) to indicate an associative table or (b) for prefixing, which I'll address in the next bullet. Prefixing is neither good or bad. It usually is not best. In your first db or two, I would not suggest using prefixes for general thematic grouping of tables. Tables end up not fitting your categories easily, and it can actually make it harder to find tables. With experience, you can plan and apply a prefixing scheme that does more good than harm. I worked in a db once where data tables began with tbl, config tables with ctbl, views with vew, proc's sp, and udf's fn, and a few others; it was meticulously, consistently applied so it worked out okay. The only time you NEED prefixes is when you have really separate solutions that for some reason reside in the same db; prefixing them can be very helpful in grouping the tables. Prefixing is also okay for special situations, like for temporary tables that you want to stand out. Very seldom (if ever) would you want to prefix columns.

基本数据库命名约定(和样式)(点击这里查看更详细的描述)

表名 选择简短、明确的名字，不要超过一两个单词 容易区分表 方便唯一字段名的命名以及查找和链接表 给表取单数名，而不是复数名(更新:我仍然同意这种惯例的原因，但大多数人真的喜欢复数表名，所以我的立场软化了)…请点击上面的链接

虽然很晚了，但我仍然想对列前缀发表我的意见

对于使用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.

编辑:是的，我知道用前缀命名列可以强制使用正确的用法，而我的方法依赖于程序员

表名一定要保持单数，person而不是people 我也一样 不。我见过一些糟糕的前缀，以至于声明我们处理的是一个表(tbl_)或一个用户存储过程(usp_)。后面跟着数据库名…不要这样做! 是的。我倾向于PascalCase我所有的表名