SQL: Doubt in Relationship between Tables

Navigation
3

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

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 register the 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:

In the system, you can choose an item or more for your request , as well as the quantity of this item. At the end of the order, you must return the total price of the order.

  

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

    
asked by anonymous 05.09.2017 / 13:48

2 answers

1

Everything that is related to what was / will be bought must be inside the ITEM_PEDIDO table if it is related to ITEM . Therefore, PRECO , as it is ITEM and has no relationship to the request, should be placed in the ITEM table and the QTD_ITEM that has relation to what was consumed should be in the ITEM table. The calculation of the total value 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 ITEM , I suggest creating a table with prices 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 DATA_PEDIDO column to the PEDIDO table. So the calculation would look like this: 

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.

    
05.09.2017 / 14:01
-1
  

Regarding the PRICE, should I leave it in the ITEM table or also include in the ITEM_QUEDIDO table?

Include also in the ITEM_STUDY table.

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

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

Additional notes:

  • Do not use data type float to store monetary values;
  • in the ITEM_STUDY table it is not necessary to create a specific column to be the primary key. The usual key is {REQUEST_ID, SEQ_ITEM}, where SEQ_ITEM is a sequential number for each request.
05.09.2017 / 16:49
CSS Stylize input range How to maintain the aspect ratio of the screen after being resized by the user?