What is the best way to store a varying number of links per record (product/row) in a MySQL database?

Question

I need to store a varying number of picture links for each product that I have in my database. Sometimes the products only have one link, sometimes 10 (or even more). Storing all links in one field are not a good practice as I know. But the only alternative I can think of is having x columns with many empty datasets (e.g. if the product only has one link) which is also not a proper way to do this. Maybe someone of you has another idea? If not, what is the best way to delimit all links in one field? Many thanks! Your help is very much appreciated!

Answer 1

Create a second table, that has a reference back to the product table. Store one link per row. You can create zero, one, or many rows per product.

CREATE TABLE product_links (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  link TEXT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id)
);