Recommendations for normalizing names in a database

When creating a database, it is preferable to normalize object names to facilitate structure readability and avoid potential errors during development. Keep in mind that a database is used by multiple people. Here is a list of recommendations based on professional experiences and more broadly on industry best practices.

1. Do not use SQL keywords. These are reserved by the language and their use could be a source of error when executing queries.

2. Never use special characters. Avoid spaces and accents in database, table, or field names. For example, instead of naming a field "Prénom", use "Prenom". Or "DateNaissance" or "Date_Naissance" instead of "Date de naissance".

3. Avoid abbreviations. Table and field names should be as explicit as possible.

4. Use a prefix in field names for a given table. This way, it is possible to identify at a glance which table a field belongs to.

Naming conventions for keys in a database

In the EPREUVE table, all fields are prefixed with EP.

Use a convention for naming primary and foreign keys. In the Marathon example, the ID suffix identifies a table's primary key, and the FK suffixes identify foreign keys.

Based on the previous example, the name EP_ID indicates that this field belongs to the EPREUVE table and that it is its primary key. Similarly, EP_MANIF_FK informs that this field is a foreign key to be linked with the MANIFESTATION table

The command to create a database is CREATE DATABASE.

SQL Syntax

CREATE DATABASE <database>;

Example

To create the MARATHON database, the SQL query is defined by:

CREATE DATABASE marathon;

CHARACTER SET: the character set

COLLATE: the collation

• It's the rule for comparing and ordering characters.

• It defines how MySQL sorts and compares text strings.

CREATE DATABASE marathon CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

Creating a table in SQL

The command to create a table is CREATE TABLE.

SQL Syntax

CREATE TABLE <table> (
    <field1> <data_type> <constraints>,
    <field2> <data_type> <constraints>,
    <field3> <data_type> <constraints>,
    CONSTRAINT <constraints>
);

Examples

CREATE TABLE age_category (
	ac_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
	ac_name varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL,
	ac_minimumage tinyint NOT NULL,
	ac_maximumage tinyint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE runner (
	ru_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
	ru_lastname varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL,
	ru_gender **ENUM('M', 'F') NOT NULL,**
	ac_id INT NOT NULL,
	FOREIGN KEY (ac_id) REFERENCES age_category(ac_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE club (
	cl_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
	cl_name varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE registration (
    ru_id int NOT NULL,
    re_annee year NOT NULL,
    cl_id int NOT NULL,
    PRIMARY KEY (ru_id, re_annee),
    FOREIGN KEY (ru_id) REFERENCES runner(ru_id),
    FOREIGN KEY (cl_id) REFERENCES club(cl_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Constraints in SQL

A constraint allows refining the values accepted in a given field. For example, it is possible to specify whether a field can be empty or not, or to define its default value. It is also possible to set functional integrity constraints (primary and foreign keys)

Field constraints

1. (NOT) NULL: indicates whether a field can contain the NULL value or not.

2. DEFAULT <value>: assigns a default value to a field.

3. UNIQUE: indicates that a field cannot contain duplicates.

4. CHECK (<expression>): adds a validation test for field data

To modify the creation query of the COUREUR table from example 4, adding constraints, the SQL query becomes:

CREATE TABLE COUREUR(
    CO_PRENOM VARCHAR(100) NOT NULL,
    CO_NOM VARCHAR(100) NOT NULL,
    CO_NAISSANCE DATE NOT NULL,
    CO_SEXE TINYINT NOT NULL CHECK(CO_SEXE IN (1, 2))
);

In this example, adding the NOT NULL constraint makes all fields in the COUREUR table mandatory, and the CHECK constraint specifies that the CO_SEXE field only receives the value 1 or 2

Functional integrity constraints

1. PRIMARY KEY: defines the primary key of a table.

2. FOREIGN KEY: defines a foreign key. With MySQL, FOREIGN KEY management is only supported on 'InnoDB' type tables

The previous example can be completed by adding a primary key to the COUREUR table, the SQL query becomes:

CREATE TABLE COUREUR_ENUM (
    CO_ID INT NOT NULL PRIMARY KEY,
    CO_PRENOM VARCHAR(100) NOT NULL,
    CO_NOM VARCHAR(100) NOT NULL,
    CO_NAISSANCE DATE NOT NULL,
    **CO_SEXE ENUM('M', 'F') NOT NULL**
);

Functional integrity constraints can be added in the following way in the SQL queries for creating the MANIFESTATION and CHAMPIONNAT tables:

and

CREATE TABLE MANIFESTATION(
    MA_ID INT NOT NULL PRIMARY KEY,
    MA_NOM VARCHAR(100) NOT NULL,
    MA_CHAMP_FK INT FOREIGN KEY REFERENCE CHAMPIONNAT(CH_ID)
);

UNIQUE:

The combination of multiple columns must be unique, even if each field taken individually can contain duplicates.

CREATE TABLE COUREUR (
    CO_ID INT PRIMARY KEY,
    CO_PRENOM VARCHAR(100),
    CO_NOM VARCHAR(100),
    CO_NAISSANCE DATE,
    UNIQUE (CO_PRENOM, CO_NOM, CO_NAISSANCE)
);

The CONSTRAINT clause

The CONSTRAINT clause in SQL

The CONSTRAINT clause is used when a constraint concerns multiple fields or when you want to name a constraint. This clause can be used for both field constraints and integrity constraints.

SQL Syntax

CREATE TABLE <table>
...
CONSTRAINT <constraint_name> <constraint>

Example

To add a constraint on the CHAMPIONNAT table, created in the example, to test that the start date is earlier than the end date, the SQL query is:

CREATE TABLE CHAMPIONNAT(
    CH_ID INT NOT NULL PRIMARY KEY,
    CH_NOM VARCHAR(100) NOT NULL,
    CH_DATEDEB DATE NOT NULL,
    CH_DATEFIN DATE NOT NULL,
    CONSTRAINT CH_DATE_CHK CHECK(CH_DATEDEB<CH_DATEFIN)
);

Modifying tables with ALTER TABLE in SQL

The ALTER TABLE command modifies the structure of a table. It is possible to modify, delete, or add a field. It is also possible to add a primary or foreign key

SQL Syntax

ALTER TABLE <table> <instruction> ;

Adding a field

It is very simple to add a field to a table, using the ADD or ADD COLUMN keyword.

SQL Syntax

ALTER TABLE runner ADD COLUMN ru_gender **ENUM('M', 'F') NOT NULL** ;

Example

If the CH_NOM field was forgotten when creating the CHAMPIONNAT table, the SQL query becomes:

ALTER TABLE CHAMPIONNAT ADD COLUMN CH_NOM VARCHAR(100) NOT NULL;

Adding a primary or foreign key

It is possible to specify that an existing field in a table is a primary key or a foreign key using the ADD PRIMARY KEY or ADD FOREIGN KEY commands. If the key is composed of multiple fields or if you want to name it, it is necessary to use the ADD CONSTRAINT command

ALTER TABLE <table>
ADD PRIMARY KEY (<field>) ;
ADD CONSTRAINT <key> PRIMARY KEY (<field>) ;
ADD FOREIGN KEY (<field>) REFERENCES <table> (<field>)
ADD CONSTRAINT <key> FOREIGN KEY (<field>) REFERENCES <table> (<field>) ;

To add the keys present in the MANIFESTATION table, the SQL queries are:

ALTER TABLE MANIFESTATION ADD PRIMARY KEY (MA_ID);
ALTER TABLE MANIFESTATION ADD FOREIGN KEY (MA_CHAMP_FK) REFERENCES CHAMPIONNAT(CH_ID)

Modifying a field's data type

The command to modify a field's data type is MODIFY COLUMN.

SQL Syntax

ALTER TABLE <table> MODIFY COLUMN <field> <new_data_type> ;

Example

To modify the data type of the CH_NOM field in the CHAMPIONNAT table, the SQL query is:

ALTER TABLE CHAMPIONNAT MODIFY COLUMN CH_NOM VARCHAR(50);

Deleting a field

It is possible to delete a field using the DROP COLUMN command.

SQL Syntax

ALTER TABLE <table> DROP COLUMN <field> ;

Example

To delete the CH_NOM field from the CHAMPIONNAT table, the SQL query is:

ALTER TABLE CHAMPIONNAT DROP COLUMN CH_NOM;

Deleting a constraint

The command to delete a constraint is DROP followed by PRIMARY KEY or FOREIGN KEY for functional integrity constraints, and CONSTRAINT for field constraints.

SQL Syntax

ALTER TABLE <table>
DROP PRIMARY KEY ;
DROP FOREIGN KEY <field> or <key> ;
DROP CONSTRAINT <key> ;

It is not necessary to name the primary key to delete since it is unique.

Example

To delete the primary key and foreign key from the MANIFESTATION table, here are the SQL queries:

ALTER TABLE CHAMPIONNAT DROP PRIMARY KEY;
ALTER TABLE MANIFESTATION DROP FOREIGN KEY MA_CHAMP_FK;

Renaming a field

The RENAME COLUMN command allows renaming a field.

SQL Syntax

ALTER TABLE <table> RENAME COLUMN <field> TO <new_field> ;

Example

To rename the CH_DATEDEB field in the CHAMPIONNAT table, the SQL query is:

ALTER TABLE CHAMPIONNAT RENAME COLUMN CH_DATEDEB TO CH_DATEDEBUT;

Renaming a table

To rename a table, simply use the RENAME command.

SQL Syntax

ALTER TABLE <table> RENAME <new_table> ;

To rename the CHAMPIONNAT table, the SQL query is:

ALTER TABLE CHAMPIONNAT RENAME COMPETITIONS;

Deleting a table: DROP TABLE

The DROP TABLE command deletes a table.

SQL Syntax

DROP TABLE <table> ;

Example

To delete the MANIFESTATION table, the SQL query is:

DROP TABLE MANIFESTATION;

Deleting a database: DROP DATABASE

The command to delete a database is DROP DATABASE.

SQL Syntax

DROP DATABASE <database> ;

Example

To delete the MARATHON database, the SQL query is:

DROP DATABASE MARATHON;