How to handle vectors in a Mysql database?

Asked

Viewed 5,177 times

4

I have a supermarket box application in which after the end of the sale need to insert a record with data related to this sale in a table, in this record must be included the code of the products purchased and the quantity of each.

Since I am using a Mysql database there are no vectors (arrays), how should I proceed in a situation like this? There is a good practice that can be applied to this case?

Here’s what I thought I’d do, but I think this is a scam:

codigo    vendedor    cod_produtos    quant_produtos    data
     1           3     "1,5,3,6,9"     "5,10,7,13,2"    12/12/2013

In this case the fields cod_products and quant_products are of the char/varchar/nvarchar type and if you need to get the data to generate a report or something would use something like string[] codigos = codProdutos.Split(','); (in C#), to obtain the data in vector form.

mysql array

  • You can modify your database?!

  • @Felipeoriani yes, anything.

  • Good practice is to follow the normal forms http://www.luis.blog.br/primeira-forma-normal-1fn-normalize-data.aspx

  • There is something called "Normalization". Have a look at: http://www.macoratti.net/cbmd1.htm

  • Vlw for the tip and @Ecil.

  • What you would like to do is possible in Nosql banks. In them you can store collections in a field. Relational databases require improved modeling techniques.

Show 1 more comment

4 answers

10


You have to create an associative table, is the most correct way to do, example:

tbl_venda_assoc_product

fk_venda  fk_produto quantidade
 1           1           5
 1           2           1

and in your sales table you keep the code, seller and date.

Why do it this way? 1 order can have multiple products and a product can be in multiple orders, ie an association n pra n, requires an associative table...

5

When I went through similar situations I did something like:

codigo    vendedor produto  quantidade data
 1           3     1        5          12/12/2013
 1           3     5        10         12/12/2013
 1           3     3        7          12/12/2013
 1           3     6        13         12/12/2013
 1           3     9        2          12/12/2013

Since product is part of the key table. Imagine a query wondering how much was sold of a product in total using the example you suggested and my suggestion.

1

The ideal would be to add a table to associate the relationship between Sale and Product, and in this apply the quantity, example:

FK_VENDA   FK_PRODUTO    QUANTIDADE
1          1             5
1          5             10
1          3             7
1          4             13
1          9             2

Whereas you own the table VENDA and PRODUTO, the script would look something like this:

CREATE TABLE VendaItem
(
FK_VENDA int,
FK_PRODUTO int,
QUANTIDADE int,
FOREIGN KEY (FK_VENDA) REFERENCES VENDA(ID),
FOREIGN KEY (FK_PRODUTO) REFERENCES PRODUTO(ID)
);

0

In terms of SQL the correct one would be to separate the tables according to the entities involved:

  • Sale: code (PK), seller, date
  • Product_sale: sales code (PK, FK), product code (PK, FK), quantity, value_unitario

In this case, the Product_sale table has a relationship N:1 (N for 1) with the Sale table. That means, each Sale can have several Products.

Browser other questions tagged mysql array

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