Pular para o conteúdo principal

SQL: Naming external identification code fields at database.


Demand

Use "id_{context}" to name fields of external identification codes and "{context}_id" to internal ones.


Description

When creating a column in a table that reference an id (identification code) of a table located at the database you are dealing it should be named as "{Table Name}_id".

When creating a column in a table that reference an id of a context external to the database you are dealing with  it should be named as "id_{Context Name}".

Examples

1:  CREATE TABLE customer (
2:      id INT NOT NULL,
3:      name VARCHAR(100) NOT NULL,
4:      id_ssn COMMENT 'the United States, the Social Security number',
5:      user_id INT NULL COMMENT 'if the customer have a system login it will be refernced here',
6:     PRIMARY KEY (id),
7:     UNIQUE `uq_ssn` (`ssn`),
8:     CONSTRAINT `fk_customers_user_id_users_user_id`
9:         FOREIGN KEY (`user_id`) 10:     
REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION 11: );


Examples Explanation

If a column in table "table1" references a table in the same database, such as e.g., "table2", then such a column in "table2" must be named by entering the name of the table where the key related to the column being created is located followed by an "id". In this example, it would be "table1.table2_id" (as used in most of the companies nowadays) and when we reference an "id" column in an external context we start with the string "id" followed by the name of the column or context that the id relates to. In our example the SSN field most of the times are just named as "ssn" and in the upper CREATE TABLE it was named "id_ssn" and anyone looking at it, even non americans, would then know that it is not an item originated in our database and that the column is an ID or could be used as an ID but it is not referencing a data, context or table inside the domains of that database.

Comentários

Postagens mais visitadas deste blog

Article: Preventing Database Gridlock: Recognizing and Resolving Deadlock Scenarios

Learn how deadlocks occur in database systems, understand their impact on performance, and discover practical techniques for identifying potential deadlock scenarios in your SQL code.

NestJS Clustering: Advanced techniques to increase NestJS Apps response

In today’s fast-evolving digital landscape, performance and scalability have become the lifeblood of modern applications. As advanced JavaScript developers, we continuously seek strategies to optimize our code and leverage every ounce of our infrastructure. NestJS clustering promises not just incremental improvements but exponential enhancements by fully unlocking the potential of your virtual machines. This article delves into NodeJS clustering within the NestJS framework, exploring its theoretical underpinnings, offering practical, code-rich examples, and even highlighting an experimental twist that can set your application apart. Unlocking the Power of NodeJS Clustering At its core, NodeJS clustering is about taking full advantage of multi-core systems. Typical NodeJS apps run as single-threaded processes, which can be a bottleneck when you need to handle a massive number of concurrent operations. Clustering allows you to spin up multiple worker processes that share the same server ...

General: Prevent arbitrary precision arithmetic

Demand Some simple decimal calculations like 4.6 * 100 may lead in irregularities when dealing with numbers. Description During arithmetic operations like addition, subtraction, multiplication, and division, the numbers are manipulated according to the rules defined by the IEEE 754 standard. However, due to the finite precision of the representation, rounding errors may occur, leading to small discrepancies between the expected and actual results of computations. In simpler terms, the computer operates using powers of 2 (binary-based), so whenever we need to work with a number that cannot be exactly represented in binary (like 0.1, which is a base-10 fraction), it uses an approximation rather than an infinite number and is incapable of presenting the exact correct value. To prevent it we may use some libraries to prevent the problem. In Javascript and Typescript we have the BigNumbers library (and others). PHP has a built-in library for arbitrary-precision arithmetic called BCMath (Bin...