How to deal with vectors in a MySQL database?
I have an application that supermarket cashier in which after the end of the sale needs to enter a record with data regarding 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? Is there a good practice that can be applied to this case?
I had thought in 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 type would use something like string[] codigos = codProdutos.Split(','); (in C#), to get the data in vector form.
4 answers
You have to create an associative table, it is the most correct way to do it, 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, that is, an association N to n, requires an associative table...
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 wondering how much a product sold in total using the example you suggested and my suggestion.
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
Considering 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)
);
In terms of SQL the correct would be to separate the tables according to the entities involved:
- sale: code (PK), seller, date
- Product_selling: code_selling (PK, FK), cod_product (PK, FK), quantity, unit_value
In this case, the Product_sale table has a relationship N: 1 (N to 1) with the sale table. This means that, each sale can have several Product_sale.