Best Practices

Properly designing a database from the beginning prevents errors, rework, and ensures long-term efficiency. This document explains how to name fields clearly and how to select the right data type for each one.

1. What are fields and data types?

• Field: It is a column in a table. It represents a characteristic of the data being stored. For example, in a users table, fields can be name, email, age.
• Data type: Defines the type of information that can be stored in that field, such as text, number, date, or boolean.

2. Best practices when naming fields

Use clear and descriptive names

Incorrect:

SELECT a, b FROM table;

Correct:

SELECT username, email_address FROM users;

Use a single language

Incorrect:

SELECT nombre, email, birth_date FROM users;

Correct:

SELECT name, email, birth_date FROM users;

Follow a naming convention

• snake_case: uses underscores. Example: full_name
• camelCase: uses inner uppercase letters. Example: fullName

Choose one and keep it throughout the project.

3. How to choose the right data type

Each field must have a data type that matches the value it will store.

Data Type	Recommended use	Example
INT	Integer numbers (age, ID, counts)	age = 25
DECIMAL(10,2)	Numbers with decimals (prices, money)	price = 19.99
VARCHAR(n)	Short text (name, email)	name = 'Ana'
TEXT	Long text (descriptions)	description = 'Long description'
DATE	Dates (birth, creation, events)	birth_date = '2000-05-12'
BOOLEAN	True or false	active = true

4. Key design rules

Use NOT NULL if the field is mandatory

email VARCHAR(100) NOT NULL

Define primary keys (PRIMARY KEY)

id INT PRIMARY KEY AUTO_INCREMENT

Use default values (DEFAULT)

active BOOLEAN DEFAULT true
creation_date DATE DEFAULT CURRENT_DATE

Use foreign keys (FOREIGN KEY) for relationships between tables

user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)

5. Common errors to avoid

Bad design	Associated problem
Using VARCHAR for everything	Inefficiency and lack of validation
Generic fields like data1	Lack of clarity
Storing multiple data together	Makes analysis and filtering difficult
Mixing languages or styles	Confusion and development errors

6. Example of a well-designed table

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  age INT,
  birth_date DATE,
  active BOOLEAN DEFAULT true,
  registration_date DATE DEFAULT CURRENT_DATE
);

7. Conclusions

• Use clear and consistent names in fields
• Choose the appropriate data type according to the context
• Apply constraints like NOT NULL, DEFAULT, PRIMARY KEY
• Always think about the clarity and future maintenance of the database

A well-designed database is essential for the success of any system or application.