Quelles sont les meilleures pratiques pour nommer des tables, des colonnes et des contraintes dans un schéma de base de données relationnelle ?

The correct answer is that there is no right or wrong when naming database tables — just be consistent from the start. However, Narayana “Vyas” Kondreddi (long-time DBA and SQL Engineer) wrote back in 2001: “Tables represent the instances of an entity. For example, you store all your customer information in a table. Here, ‘customer’ is an entity and all the rows in the customers table represent the instances of the entity ‘customer’. So, why not name your table using the entity it represents, ‘Customer’. Since the table is storing ‘multiple instances’ of customers, make your table name a plural word.”

When naming in databases, think clarity and simplicity. For tables, use singular names like 'User'. Columns should be descriptive yet concise, like 'emailAddress'. Stick to a style, be it camelCase or snake_case. Link foreign keys to their origin, like 'userId' in an 'Order' table. Primary keys are usually just 'id', but get creative with compound keys. Name constraints and indexes clearly, with hints of their role, like 'fk_orders_userId'. Avoid repeating table names in columns. Consistency is your friend across the database, and keep in mind your DBMS's case rules. Lastly, document your naming scheme; it's a roadmap for others. Keep it neat and logical, and you're set!

I agree to use proper meaningful naming convention for ease of use. Like, -Plural forms for table names. -Employing pk_ & fk_ for primary & foreign key -Separating multi-word column names with _ -Prefixes "sp_", "udf_", "trg_", and "vw_" for stored procedures, user-defined functions, triggers & views. I have seen, in some databases, tables like c_records, t_records, a_records (for Customers, Transactions, and Addresses). Similarly not using generic column names like Id, Email, FirstName etc.. to mitigate common attackers.

A table represents a collection of entities of the same type, such as customers, orders, or products. Therefore, it makes sense to use a singular noun for the table name, rather than a plural one. This way, you can avoid confusion when writing queries, joining tables, or creating foreign keys. For example, instead of naming your tables customers, orders, and products, you should name them customer, order, and product.

Use names that accurately describe the data in the column. For example, instead of using "date," use "order_date" or "shipment_date." Use short, clear names that are easy to read and understand. Avoid using long, complicated names that are difficult to remember or type. Use the same naming convention for all columns in the same table. For example, if you use underscores to separate words in one column name, use the same convention for all other column names. While abbreviations can save space, they can also be confusing. Use full words whenever possible to avoid misunderstandings. Column names should be singular nouns, not plural. For example, use "product_id" instead of "products_id."

I like to prefix constraint names with its type to have appropriate sorting in any tool. PK_ - prefix for primary key constraint, indicates the column is the primary key for the table, e.g. "PK_customer_id". FK_ - prefix for foreign key constraint, indicates that the column is a foreign key referencing another table, e.g. "FK_order_customer_id". CHK_ - prefix for check constraint, indicates that the column has a check to limit the values can be inserted, e.g. "CHK_order_quantity_gt_zero". IX_ - prefix for an index, indicates that the column has an index created on it, e.g. "IX_customer_name". UQ_ - prefix for a unique constraint, indicates that the column has a unique constraint to ensure each value is unique, e.g. "UQ_customer_email".

Using consistent naming conventions and case styles ensures uniformity and clarity in your database schema. Define clear rules, such as using lower_case_with_underscores for tables and columns, and UPPER_CASE_WITH_UNDERSCORES for constraints. This consistency makes your schema easier to follow, enhances compatibility across platforms and tools, and prevents confusion. Avoid mixing different conventions to maintain a streamlined and professional approach, improving readability and maintenance for developers and database administrators.

When naming tables, columns, and constraints in a relational database schema, follow best practices to ensure clarity and consistency. Use descriptive, meaningful names that convey the purpose of each element, such as customer_orders for a table and order_date for a column. Employ a consistent naming convention, such as using lowercase with underscores or CamelCase. Prefix constraints with their type, like pk_ for primary keys and fk_ for foreign keys. Avoid abbreviations and reserved keywords to maintain readability and avoid confusion.

Organization of columns with similar data types together in the table can be beneficial for query performance, especially when queries involve selecting specific columns or aggregating data. It's a common practice in database design to group columns with the same or similar data types to optimize storage and retrieval.

Using primary key constraints to uniquely identify each row in a table, and foreign key constraints to enforcing referential integrity between related tables is essential for ensuring data integrity, consistency, performance, and maintainability. Primary key constraints ensure that each row in a table is unique and can be identified by a unique key value. It can be used as indexes, which can improve the performance of queries that use the key values. Foreign key constraints ensure that relationships between tables are valid and that data is not inserted into a table unless the corresponding data exists in the referenced table. It can also help to improve query performance by allowing the database to efficiently join related tables.

Resist the pressure from developers to provide a separate DB or schema for each and every micro service. Databases need to be managed differently than the rest of the architecture. They have state. They can’t be spun up and down at-will. You need different rules for scaling and HA/DR. There is value in sharing data between services, thanks to relational constraints. The cost of duplicating data is significant, as each byte of unique data is replicated many times due to clustering and backups. Varchar GUIDs/UUIDs should be used sparingly, and not as primary keys.