SQL: Doubt in the Relationship between Tables

Question

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

Table of Menu Items:

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 Order Items, where:

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 this::

On the system, the user will be able to choose a item or more to your request, as well as the quantity of this item. At the end of the order, you must return the price total of it.

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

Answer 1

Everything related to what was/will be purchased should be inside the table ITEM_PEDIDO if related to ITEM. Thus the PRECO, how is it of ITEM and is unrelated to the request, should be placed in the table ITEM and the QTD_ITEM that has to do with what was consumed should be in the table ITEM. The calculation of the total value shall 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 ITEM, I suggest creating a price list with the current date as follows:

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

And the addition of the column DATA_PEDIDO on the table PEDIDO. So 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 major problems and without having to replicate data.

Answer 2

As for the PRICE, I must leave in the ITEM table or include also in the ITEM_PEDIDO table?

Also include in ITEM_PEDIO table.

The ITEMS table contains the prevailing price for each item. For each order item, both the quantity of units requested and the price in force at the time of the order should be maintained. And the reason is quite simple: prices vary over time.

Suppose item A is priced $10 on day D1 and day D2 is priced $20. If a P1 order of 3 units for item A was placed on day D1, then the order value for that item was $30. If the item price value is stored only in the ITEMS table and if on day D2 it is to see how much was the order value P1, the query will return $60 for item A, which is wrong! That is why in the commercial order systems for each item is always stored the unit value of the item at the time of order.

Additional remark:

• do not use data type float to store monetary values;
• in the ITEM_PEDIDO table it is not necessary to create a specific column to be the primary key. The usual key is {ID_PEDIDO, SEQ_ITEM}, where SEQ_ITEM is a sequential number, for each request.