SQL: Doubt in the Relationship between Tables

Question:

I'm creating an academic system that consists of a type of virtual restaurant menu, for that, I created three tables in the bank:

Menu Items Table:

CREATE TABLE ITEMS(
    ID INT PRIMARY KEY NOT NULL,
    NOME VARCHAR(20) NOT NULL,
    DESCRICAO VARCHAR(20),
    PRECO FLOAT NOT NULL
)

Order Table:

CREATE TABLE PEDIDO(
    NUMERO INT PRIMARY KEY NOT NULL,
    MESA INT NOT NU NULL,
    VALORTOTAL FLOAT
)

Table where I record the Order Items, in which:

ID_PEDIDO = FK de Pedido, ID_ITEM = FK de Item
CREATE TABLE ITEM_PEDIDO(   
    ID INT PRIMARY KEY NOT NULL,
    ID_PEDIDO INT NOT NULL,
    ID_ITEM INT NOT NULL,
    QTD_ITEM INT
 )

My question is:

In the system, the user can choose one or more item for his order , as well as the quantity of this item. At the end of the order, the total price of the order must be returned.

So, should I leave the QTD_ITEM in the ITEM_PEDIDO table? As for the PRICE, should I leave it in the ITEM table or do I also include it in the ITEM_PEDIDO table?

Answer:

Everything that is related to what was/will be purchased must be inside the ITEM_PEDIDO table if it is related to the ITEM . Therefore, the PRECO , as it belongs to the ITEM and is not related to the order, must be placed in the ITEM table and the QTD_ITEM that is related to what was consumed must be in the ITEM table. The calculation of the total amount will be as follows:

SELECT P.NUMERO,
       SUM(IP.QTD_ITEM * I.PRECO) AS VALOR_TOTAL
  FROM PEDIDO P
       INNER JOIN ITEM_PEDIDO IP ON IP.ID_PEDIDO ON P.NUMERO
       INNER JOIN ITEMS I ON I.ID = IP.ID_ITEM
 WHERE P.NUMERO = 'X'
 GROUP BY P.NUMERO

If you want to keep a historical price of an ITEM , I suggest creating a table with the prices with the current date as follows:

╔════════════════════════════╗
║         ITEM_PRECO         ║
╠═════════════════╦══════════╣
║ ID_ITEM         ║ INTEIRO  ║
║ INICIO_VIGENCIA ║ DATA     ║
║ FINAL_VIGENCIA  ║ DATA     ║
║ PRECO           ║ NUMÉRICO ║
╚═════════════════╩══════════╝

And adding the column DATA_PEDIDO in Table PEDIDO . Therefore, the calculation would be as follows:

SELECT P.NUMERO,
       SUM(IP.QTD_ITEM * IP.PRECO) AS VALOR_TOTAL
  FROM PEDIDO P
       INNER JOIN ITEM_PEDIDO IP ON IP.ID_PEDIDO ON P.NUMERO
       INNER JOIN ITEMS I ON I.ID = IP.ID_ITEM
       INNER JOIN ITEM_PRECO IP ON IP.ID_ITEM = I.ID
                               AND P.DATA_PEDIDO BETWEEN IP.INICIO_VIGENCIA AND IP.FINAL_VIGENCIA
 WHERE P.NUMERO = 'X'
 GROUP BY P.NUMERO;

So you can make price changes without any major problems and without having to replicate data.

Post Views: 0
Scroll to Top