Convention for attributes that preserve types

Question

Convention for attributes that preserve types

Navigation

#1 by (2 votes)

0

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

Another table present in the database is posts . Each publication can (or does not) have a certain type: book, periodical, event, tcc and others.

It is true that in each table there will be a column (attribute) to keep the type of the given record.

Below are two suggestions for the value of these attributes:

abbreviations (strings)

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

publication.tipo = 'liv' || Publication Type = 'tcc' || publication.type = 'evt'

integers

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

publication.tipo = 1 || Publication Type = 2 || publication.type = 3

My question is: Is there a convention for these attributes or is it the responsibility of the project developer (s)?

mysql modelo-relacional

asked by anonymous 21.07.2015 / 22:38

1 answer

Receive select fill in jQuery How to create arrow-shaped borders on the sides of a div

score 2 · Accepted Answer

There are standard rules for database normalization:

First Normal Form (or 1FN) requires that all column values in a table be atomic (example: a number is an atom, while a list or a set is not). Normalizing to the first normal form deletes repeating 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, part of the candidate key;
Third Normal Form (or 3FN) requires that there be no non-trivial functional dependencies of non-key attributes on anything but a superset of a candidate key; Normal Form of Boyce-Codd (or BCNF) requires that there is no non-trivial functional dependency of attributes on anything more than a superset of a candidate key. At this stage, all attributes are dependent on a key, an integer key, and nothing but a key (excluding trivial dependencies such as A → A);
Fourth Normal Form (or 4FN) requires that there is no non-trivial multi-valued dependency of attribute sets on anything more than a superset of a candidate key;
Fifth Normal Form (or 5FN or PJ / NF) requires that there be no non-trivial joins dependencies that do not come from key constraints;
Domain-Key Normal Form (or DK / NF) requires that all constraints follow key domains and constraints.

Responding to your specific issue:

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