Tablestructure for fast inserts/deletes with foreign keys

Current Situation

We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product.

Example:

id | competitors | some infos about competitor | product
---------------------------------------------------------------
1 | C_1 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_1
2 | C_2 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_1
3 | C_3 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_1
4 | C_1 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_2
5 | C_4 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | P_2

Every 30 minutes we read in the new competitors. To do so we first delete everyone with the specific product (P_1) and insert afterwards the new ones. So there will never be a competitor in our DB, which isnt at the moment listed for this product.

When we got the new infos, we do some kind of research with the products.

(We also have a table called p with ca. 100,000 rows. In that table we store the infos about the products.)

So back to the research process. We run through every product an have to get the competitors with their infos. For this purpose we have do do a mysql request like SELECT * FROM c WHERE product LIKE "P_1";. Not to say that this is not very performant.

My Goal

I want to achieve, that with only one query i got the product information from p an also the competitor ones from c where the product id is the same.
So that these infos get stored in an array like this:

[0]['id'] => '2'  
[0]['product_identifier'] => 'P_1'  
[0]['c'][0]['competitors'] => 'C_1'  
[0]['c'][1]['competitors'] => 'C_2'  
[0]['c'][3]['competitors'] => 'C_3'

Possible Solution

To get the needed information with just one query, i came up with this solution.

In table c the product row contains a foreign key which links to the p table. That would be very easy to realise. But I am asking myself if it would be possible to delete c or p rows easaly. I never worked tha

Don't be scared to create a foreign key in a database. One of the biggest reasons why you should use a foreign key is to enforce referential integrity. Depending on your foreign key it can be possible to use the CASCADE option to delete all competitors for a product when you delete a product (so it makes it easy to manage).

If you set your tables and foreign key up properly the DELETE statement to delete a product and its competitors should something like:

DELETE FROM Product WHERE Product.Product_identifier = [insert your identifier here];

For an example about the DELETE CASCASE option of a foreign key I suggest you read the following article: http://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/

To list a product and all competitors you can use:

SELECT Product.product_identifier, Competitors.[some infos about competitor]
FROM Product
LEFT JOIN Competitors 
   ON Product.Product_identifier = Competitors.Product 
WHERE Product.Product_identifier = [insert your identifier here];

StackExchange Database Administrators Q#29872, answer score: 2