Convention for attributes that preserve types

Question

A table stores data from teachers. Each teacher has a degree: specialist, master, doctor, post-doctor and others.

Another table present in the base is the publications. Each publication may (or may not) have a certain type: book, journal, event, tcc and others.

It is certain that in each table there will be a column (attribute) to maintain the guy of the particular record.

Below are two suggestions for the value of these attributes:

1. abbreviations (strings)

   teacher.type = 'mes' || teacher.type = 'dr' || teacher.type = 'esp'

   publication.type = 'Liv' || publication.type = 'tcc' || publication.type = 'evt'

2. whole

   teacher.type = 1 || teacher.type = 2 || teacher.type = 3

   publication.type = 1 || publication.type = 2 || publication.type = 3

My point is: there is some convention for these attributes or the decision is the responsibility of the developer(s) of the project(s)?

Answer 1

Standard rules for database normalization exist:

• First Normal Form (or 1FN) requires all column values in a table to be atomic (example: a number is an atom, while a list or set is not). Normalization for the first normal form eliminates repeated groups by placing them each in a separate table, connecting them with a primary or foreign key;
• Second Normal Form (or 2FN) requires that there is no non-trivial functional dependency on an attribute other than the key, in part of the candidate key;
• Third Normal Form (or 3FN) requires no non-trivial functional dependencies of attributes that are not key, on anything other than a superset of a candidate key; Boyce-Codd Normal Form (or BCNF) requires that there is no nontrivial functional attribute dependency on anything more than a superset of a candidate key. At this stage, all attributes are dependent on a key, an entire key and nothing more than a key (excluding trivial dependencies such as A A);
• Fourth Normal Form (or 4FN) requires that there is no nontrivial multi-valued dependency of attribute sets on anything more than a superset of a candidate key;
• Fifth Normal Form (either 5FN or PJ/NF) requires that there are no non-trivial joins (associations) dependencies that do not come from key restrictions;
• Domain-Key Normal Form (or DK/NF) requires all restrictions to follow key domains and restrictions.

Responding to your specific problem:

CREATE TABLE titulo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(255) NOT NULL
);

CREATE TABLE professor (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(255) NOT NULL,
    titulo_id INT,
    FOREIGN KEY titulo_key (titulo_id) REFERENCES titulo(id)
);

Source for normal rules: Link