How to avoid duplication of content in a table without a primary key?

Question

How to avoid duplication of content in a table without a primary key?

Navigation

#1 by (2 votes)

4

In a virtual store panel that I am building the products have relationships by color and size, where each product color has a size. This is a part of my database for a better understanding of the problem: link
When the user wants to associate the colors with a product in the panel it is forwarded to a page with all the colors (checkbox list) and in it he selects the colors and sends the form to php that processes with the following script :
Note: $database is an instance of DBAL Doctrine

$checking = [];
$colors   = (array) $_POST['colors'];

$ids = [];
foreach($colors as $color) if(is_numeric($color)) $ids[]=$color;

$emptyTableQuery = $database->createQueryBuilder();
$emptyTableQuery -> delete('product_colors')
                 -> where('id_product = :idProduct AND id_color NOT IN (:ids)')
                 -> setParameters([':idProduct'=>$id,':ids'=>implode(',',$ids)]);

$emptyTable = $emptyTableQuery->execute();

foreach($colors as $color)
    $checking[] = $database->insert(
        'product_colors',
        [
            'id_product'=> $id,
            'id_color'=> $color
        ],
        ['id_product'=>\PDO::PARAM_INT,'id_color'=>\PDO::PARAM_INT]
        );

When a new product is inserted the code works perfectly, however, when the colors of the product are updated the colors already existing in the database are duplicated ...

Before the current code ALL colors were removed from the table, or it was completely clean, which resulted in a problem greater: there is a third relation with the table called 'sizes_by_product_color' where sizes are stored for each color and the respective stocks, that is, if I deleted everything too these data were lost and the customer would have to register all sizes for colors, before existing, again ...

This is part of my database for a better understanding of the problem: link

How could I solve my problem in a practical way? is there a solution for SQL or will I have to do a select to do the check in PHP?

php mysql sql doctrine-2

asked by anonymous 20.05.2015 / 16:44

1 answer

Problem loading image in HTML (Performance) HTML Print - PHP

score 2 · Accepted Answer

I do not have much experience in PHP, but I have SQL, so I'll share my opinion:

In your code, when you save a product, all colors in a product that are not present in the selected list are removed from the database. Then, relationships between product and color are added for each selected color.

This will always work the first time, since there are no colors related to a product, so there is no chance of conflict. However, when you update a product that already has relationships with colors, you are trying to insert repeated colors. See:

$id = 1;

-INSERÇÂO:
$colors = {"#000000","#FFFFFF"};

    -REMOÇÃO DE product_color ONDE product_id = $id E color_id NÃO ESTEJA EM $colors:
    Nada é feito. Não existem registros em product_color com product_id = $id 
    -ADIÇÃO DE UM product_color PARA CADA $colors:
    product_colors = 
    product_id | color_id
        1      | "#000000"
        1      | "#FFFFFF"

-ATUALIZAÇÃO:  
$colors = {"#000000","#112233"};    

    -REMOÇÃO DE product_color ONDE product_id = $id E color_id NÃO ESTEJA EM $colors:
    product_id | color_id
        1      | "#000000"

    -ADIÇÃO DE UM product_color PARA CADA $colors:
    product_colors = 
    product_id | color_id
        1      | "#000000" <-- Já existia e não foi deletado!
        1      | "#000000" 
        1      | "#112233"

The easiest solution I can think of is, instead of deleting only the colors that were not selected in product_colors, delete them all. They will be inserted again in the next statement anyway.

If you do not want to do this, you probably will not escape selecting the colors in the product after the delete and inserting only the colors that are not in the list.

Editing: After some research, I came to the conclusion that the general recommendation is to check the bank to see which colors are already related to the product and not to insert them. In theory, it would be possible to use the INSERT IGNORE clause of MySql if you could mark both columns as UNIQUE , but from what I read, Doctrine does not have a representation for this function (which is understandable, since it is a component to abstract the DB, not a specific implementation of MySql). My top references were this , this and this , if you are interested in delving deeper into the reasons given. All links are for the English OS.