Database, Table and Column Naming Conventions?

Ok, since we're weighing in with opinion:

I believe that table names should be plural. Tables are a collection (a table) of entities. Each row represents a single entity, and the table represents the collection. So I would call a table of Person entities People (or Persons, whatever takes your fancy).

For those who like to see singular "entity names" in queries, that's what I would use table aliases for:

SELECT person.Name
FROM People person

A bit like LINQ's "from person in people select person.Name".

As for 2, 3 and 4, I agree with @Lars.

I recommend checking out Microsoft's SQL Server sample databases: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

The AdventureWorks sample uses a very clear and consistent naming convention that uses schema names for the organization of database objects.

1. Singular names for tables
2. Singular names for columns
3. Schema name for tables prefix (E.g.: SchemeName.TableName)
4. Pascal casing (a.k.a. upper camel case)

I work in a database support team with three DBAs and our considered options are:

1. Any naming standard is better than no standard.
2. There is no "one true" standard, we all have our preferences
3. If there is standard already in place, use it. Don't create another standard or muddy the existing standards.

We use singular names for tables. Tables tend to be prefixed with the name of the system (or its acronym). This is useful if the system complex as you can change the prefix to group the tables together logically (ie. reg_customer, reg_booking and regadmin_limits).

For fields we'd expect field names to be include the prefix/acryonm of the table (i.e. cust_address1) and we also prefer the use of a standard set of suffixes ( _id for the PK, _cd for "code", _nm for "name", _nb for "number", _dt for "Date").

The name of the Foriegn key field should be the same as the Primary key field.

i.e.

SELECT cust_nm, cust_add1, booking_dt
FROM reg_customer
INNER JOIN reg_booking
ON reg_customer.cust_id = reg_booking.cust_id

When developing a new project, I'd recommend you write out all the preferred entity names, prefixes and acronyms and give this document to your developers. Then, when they decide to create a new table, they can refer to the document rather than "guess" what the table and fields should be called.

Late answer here, but in short:

1. Plural table names: My preference is plural
2. Singular column names: Yes
3. Prefix tables or columns:

• Tables: *Usually* no prefixes is best.
• Columns: No.

4. Use any case in naming items: PascalCase for both tables and columns.

Elaboration:

(1) What you must do. There are very few things that you must do a certain way, every time, but there are a few.

• 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.

(2) What you should probably do.

• 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) What you should consider.

• 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.