Sql - Database Modeling

Asked

Viewed 123 times

1

I would appreciate your help. I don’t know how to create tables in the SQL database

My problem is this:

I have a user with a request with several items.

I already have the table users and products

How do I create this table that contains an order and various related products?

Thank you

sql

  • If I understand correctly, and a relationship 1 to n, then you should create the requested table and put in it a foreign key to the primary key of products, if it’s not that or you want the code in sql let me know

  • Search Google < order system diagram > you’ll get some ideas.

  • @Sergioguerik: What is the database manager: SQL Server? Oracle database? Mariadb?

  • Arcashaid, if you can show me through a code number how to do this would be great. thanks

  • Jose says, the Bank is SQL

1 answer

2


Whereas its tables already created contain the following structure:

Tabela Usuarios 

PK idUsuarios int
Nome varchar(30)
NomeCompleto varchar(255)

Table Products 

PK idProdutos int
Descricao varchar(255)
ValorUnit decimal(10,4)

The new tables you want

Order table 

PK idPedidos int
FK idUsuarios int
DataPedido datetime
ValorTotal decimal(10,4)

SQL code 

create table Pedidos
(idPedidos int identity,
 idUsuarios int,
 DataPedido datetime,
 ValorTotalPedido decimal(10,4))

Order Tableproduct -> Link to the various products of the application 

PK idPedidos int
PK idProdutos int
Qtde decimal(7,4) -> Decimal para produtos fracionados por exemplo

SQL code 

create table PedidosProdutos
(idPedidos int,
 idProdutos int,
 Qtde decimal(7,4))

Please note that you can register multiple products using the same order ID. Ex: 

+---------------------------+
| PEDIDO | PRODUTO |  QTDE  |
+---------------------------+
| 1      | 1029    | 1.0000 | 
| 2      | 1023    | 3.0000 |
| 3      | 1993    | 8.3100 |  
+---------------------------+

  • It seems to me that in the Requests table it is necessary to add the user id as a foreign key, so that it is possible to identify who the request is from. // In the Requested Tableproduct I would add the unit of the item and also the unit value of the item. // When an order is made, the unit price of the product is usually recorded at the time of the order.

  • @Josédiz All these reservations are valid yes, thank you, however, it was a simple illustration of the creation of the table with the various products. If we were to make a complete survey of all the requirements, there could be many other fields.

  • 1

    Thank you very much Ismael for the example. I will try to model this way. José Diz, thank you for the complement.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.