[database] Database, Table and Column Naming Conventions?

our preference:

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

  2. Should column names be singular?
    Usually, yes, except where you are breaking normalisation rules.

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

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

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to database-design

What are OLTP and OLAP. What is the difference between them? How to create a new schema/new user in Oracle Database 11g? What are the lengths of Location Coordinates, latitude and longitude? cannot connect to pc-name\SQLEXPRESS SQL ON DELETE CASCADE, Which Way Does the Deletion Occur? What are the best practices for using a GUID as a primary key, specifically regarding performance? "Prevent saving changes that require the table to be re-created" negative effects Difference between scaling horizontally and vertically for databases Using SQL LOADER in Oracle to import CSV file What is cardinality in Databases?

Examples related to language-agnostic

IOException: The process cannot access the file 'file path' because it is being used by another process Peak signal detection in realtime timeseries data Match linebreaks - \n or \r\n? Simple way to understand Encapsulation and Abstraction How can I pair socks from a pile efficiently? How do I determine whether my calculation of pi is accurate? What is ADT? (Abstract Data Type) How to explain callbacks in plain english? How are they different from calling one function from another function? Ukkonen's suffix tree algorithm in plain English Private vs Protected - Visibility Good-Practice Concern

Examples related to naming-conventions

How to name Dockerfiles What is the difference between .yaml and .yml extension? Is there a naming convention for git repositories? What's the name for hyphen-separated case? Should I use "camel case" or underscores in python? Is there a naming convention for MySQL? What is the javascript filename naming convention? REST URI convention - Singular or plural name of resource while creating it What is the standard naming convention for html/css ids and classes? What are naming conventions for MongoDB?