How to handle vectors in a MySQL database?

Question

How to handle vectors in a MySQL database?

Navigation

#1 by (9 votes)
#2 by (5 votes)
#3 by (1 votes)
#4 by (0 votes)

4

I have a supermarket carton application where after the end of the sale needs to insert a record with data referring to that sale in a table, this record should include the code of the products bought and the quantity of each.

As I'm using a MySQL database there are no arrays, how should I proceed in a situation like this? Is there a good practice that can be applied to this case?

I had thought of doing the following, but I think this is a gambiarra:

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

In case the fields cod_products and quant_products are of type char / varchar / nvarchar 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 get the data in form of vector.

mysql array

asked by anonymous 12.12.2013 / 14:13

4 answers

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

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

12.12.2013 / 14:20

1

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

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

Considering that you have 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)
);

12.12.2013 / 14:24

0

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

Sale: code (PK), seller, date
Product_product: sales_code (PK, FK), product_product (PK, FK), quantity ,_value

In this case, the Sales_Provider table has an N: 1 (N to 1) relationship with the Sale table. This means that each Sale can have multiple Product_Products.

12.12.2013 / 14:20

Verify Connection Existence with MySQL in PHP How to use class variable as default value in php functions?

score 9 · Accepted Answer

You have to create an associative table, it's the right way to do it, for example:

tbl_venda_assoc_producto

fk_venda  fk_produto quantidade
 1           1           5
 1           2           1

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

Why do it this way? 1 request can have several products and a product can be in several requests, ie an association n for n, requires an associative table ...