How to normalize this small database?

Question

I’m trying to improve this small I TCC database of the technician, it’s very small, it’s from a small page to a trade, in which I would have a catalog of products and promotions to attract buyers to the physical store. I hope you can give me some tips.

Answer 1

Starting with Valdeir Psr’s comment, "category" and "subcategory" tables would be a single table. There would be a field parent_id that would be NULLABLE referencing the parent category.

To prevent categories from repeating, place a UNIQUE CONSTRAINT in nome and parent_id together.

There should be an image table. I don’t know if you can have different products with the same image. If you can, the relationship between "image" and "product" would be N:N, otherwise it would be 1:N. I’ll assume it’s N:N.

In the "promotion" table, I would not recommend using float for the price. I suggest NUMERIC(10, 2) or similar thing.

Would look like this:

CREATE TABLE categoria (
  id INT(11) NOT NULL AUTO_INCREMENT,
  parent_id INT(11) NULL,
  nome VARCHAR(45) NOT NULL,
  ativo TINYINT NOT NULL,
  CONSTRAINT pk_categoria PRIMARY KEY (id),
  CONSTRAINT fk_categoria_parent FOREIGN KEY (parent_id) REFERENCES categoria (id),
  CONSTRAINT uk_categoria UNIQUE (parent_id, nome)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;

CREATE TABLE imagem (
  id INT(11) NOT NULL AUTO_INCREMENT,
  nome VARCHAR(65) NOT NULL,
  CONSTRAINT pk_imagem PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;

CREATE TABLE produto (
  id INT(11) NOT NULL AUTO_INCREMENT,
  categoria_id INT(11) NOT NULL,
  nome VARCHAR(65) NOT NULL,
  descricao TEXT(3000) NOT NULL,
  data_entrada DATE NOT NULL,
  ativo TINYINT NOT NULL,
  CONSTRAINT pk_produto PRIMARY KEY (id),
  CONSTRAINT fk_produto_categoria FOREIGN KEY (categoria_id) REFERENCES categoria (id),
  CONSTRAINT uk_produto UNIQUE (nome)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;

CREATE TABLE produto_imagem (
  produto_id INT(11) NOT NULL,
  imagem_id INT(11) NOT NULL,
  CONSTRAINT pk_produto_imagem PRIMARY KEY (produto_id, imagem_id),
  CONSTRAINT fk_produto_imagem_produto FOREIGN KEY (produto_id) REFERENCES produto (id),
  CONSTRAINT fk_produto_imagem_imagem FOREIGN KEY (imagem_id) REFERENCES imagem (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;

CREATE TABLE promocao (
  id INT(11) NOT NULL AUTO_INCREMENT,
  produto_id INT(11) NOT NULL,
  preco NUMERIC(10, 2) NOT NULL,
  validade DATE NULL,
  obs VARCHAR(255) NOT NULL,
  CONSTRAINT pk_promocao PRIMARY KEY (id),
  CONSTRAINT fk_promocao_produto FOREIGN KEY (produto_id) REFERENCES produto (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;

Answer 2

With the previous answer I would make some modifications. I explain:

There is no right or wrong when normalizing a DB. When we are in college, it seems that the "right" is to normalize as much as possible and show the teacher that we know how to analyze the whole in small parts.

In the real world you have to consider performance, for example. But not only that: the layout that will be imposed on the bank, or the change that will be made to this layout, can impose large code maintenance costs on the applications that access this bank, of procedures or functions of the bank itself ... and time and money are things that do not fit well when programming a solution.

To summarize: sometimes it is necessary to trust that some "non-standardizations" are the best way out. In your case, I assumed that the Analyst is confident that your product will have between none or up to 3 images. I assume that it will not be interesting, for performance reasons, to save the image as a BLOB in the table, but as a name or path to find it in the file system, in a cloud, in an FTP mount, SSH ...

I agree that the category and sub category are a "Composite". Leaving on the same table seems better, I always do it and never had reason to do different.

On the other hand, creating image tables, that is, "normalizing too much" will have a cost when the bank is in production. With the knowledge I have today, I would lose less effort normalizing the bank and justify in my TCC that the impact of several Joins does not justify creating image tables, because the Analyst is also sure that there will be no more than 3 images per product.

CREATE TABLE categoria (
  id INT(11) NOT NULL AUTO_INCREMENT,
  parent_id INT(11) NULL,
  nome VARCHAR(45) NOT NULL,
  ativo TINYINT NOT NULL,
  CONSTRAINT pk_categoria PRIMARY KEY (id),
  CONSTRAINT fk_categoria_parent FOREIGN KEY (parent_id) REFERENCES categoria (id),
  CONSTRAINT uk_categoria UNIQUE (parent_id, nome)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;


CREATE TABLE produto (
  id INT(11) NOT NULL AUTO_INCREMENT,
  categoria_id INT(11) NOT NULL,
  nome VARCHAR(65) NOT NULL,
  descricao TEXT(3000) NOT NULL,
  imagem1 VARCHAR(255),
  imagem2 VARCHAR(255),
  imagem3 VARCHAR(255),
  data_entrada DATE NOT NULL,
  ativo TINYINT NOT NULL,
  CONSTRAINT pk_produto PRIMARY KEY (id),
  CONSTRAINT fk_produto_categoria FOREIGN KEY (categoria_id) REFERENCES categoria (id),
  CONSTRAINT uk_produto UNIQUE (nome)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;


CREATE TABLE promocao (
  id INT(11) NOT NULL AUTO_INCREMENT,
  produto_id INT(11) NOT NULL,
  preco NUMERIC(10, 2) NOT NULL,
  validade DATE NULL,
  obs VARCHAR(255) NOT NULL,
  CONSTRAINT pk_promocao PRIMARY KEY (id),
  CONSTRAINT fk_promocao_produto FOREIGN KEY (produto_id) REFERENCES produto (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;