Skip to content

GitLab

Last edited by joaofleao
Page history

banco_dados

Home Escopo e Cronograma Processo Design Configuração Arquitetura Código BD Qualidade Utilização

Banco de Dados

Descrição

A modelagem, documentação e organização do banco de dados se situará nessa seção.

Sumário

Modelagem

Esquema Conceitual

Users - have a email as a primary key to login, profile image, level points, role, name, favorite tags and favorite recipes.

Recipes – have a title, description, level difficult, user_id from user has made, status (‘P’ = pendent; ‘R’ = reproved; ’A’ = approved), numbers of likes in recipes, time of preparation, steps with title and description, images with a link and index saving the position in recipe, tags containing a title.

Esquema Lógico

Decisions: We have decided to use two types of database Firebase noSQL for user and Postgrees SQL for recipes, because is faster to load the user information in profile with noSQL and recipes have more information to storage.

Schema:

Implementação

This subsection is dedicated to elucidating the implementation of the database, be it SQL or noSQL. All implementation was done using the knex library.

Knex

Knex is a technique that is used to build queries. It supports various databases like Postgres, MySQL, SQLite, Oracle, and some others as well. It provides both callbacks and promise interface. It provides connection pooling and standardized responses.

Schemas

Thinking about the user flow, he will need to have a login to save favorite recipes and send new recipes. Because of this, we use firebase to create a user profile. The implementation of the profile follows the script defined in the logical scheme of the previous session.

Postgrees

The tables created in Postgrees SQL are made through the knex and can be found in the backend inside this directory OLIVE-RECIPES-API/src/database/migrations/.

recipes

Script for table creation:

export async function up(knex: Knex): Promise<void> {
  return knex.schema
    .hasTable('recipes')
    .then((exists) => {
      if (!exists) {
        return knex.schema.createTable('recipes', (table) => {
          table.increments('recipe_id').primary().notNullable();
          table.string('ingredients', 1000).notNullable();
          table.string('description', 1000).notNullable();
          table.integer('preparation_time').unsigned().notNullable();
          table.integer('difficulty').unsigned().notNullable();
          table.string('user_id', 255).notNullable();
          table.string('status', 20).notNullable();
          table.integer('likes_number').notNullable();
          table.string('title', 150).notNullable();
        });
      }
    });
}

Then it was necessary to add a script to change the recipes table. It is necessary to have a new column containing the information active, as the recipes will not have hard delete, only soft delete. In this case, the change follows:

export async function up(knex: Knex): Promise<void> {
  return knex.schema.alterTable('recipes', (table) => {
    table.integer('active', 1).notNullable().defaultTo(1);
  });
}

steps

All recipes need steps to be made, below the script:

export async function up(knex: Knex): Promise<void> {
  return knex.schema.hasTable('steps')
    .then((exists) => {
      if (!exists) {
        return knex.schema.createTable('steps', (table) => {
          table.increments('step_id').primary().notNullable();
          table.string('title', 100).notNullable();
          table.string('description', 1000).notNullable();
          table.integer('position').notNullable();
          table.integer('recipe_id').references('recipes.recipe_id').onDelete('CASCADE').onUpdate('CASCADE');
        });
      }
    });
}

tags

At this point, we need put some tags, so here was the script:

export async function up(knex: Knex): Promise<void[]> {
  return Promise.all([
    knex.schema
      .createTable('tags', (table) => {
        table.increments('tag_id').primary().notNullable();
        table.string('title', 100).notNullable();
      }),
    knex.schema
      .createTable('recipes_tags', (table) => {
        table.increments('id').primary().notNullable();
        table.integer('recipe_id').references('recipes.recipe_id').onDelete('CASCADE').onUpdate('CASCADE');
        table.integer('tag_id').references('tags.tag_id').onDelete('CASCADE').onUpdate('CASCADE');
      }),
  ]);
}