Structuring in the bank the paths to the images and videos of my product?

Question

How can I structure in the database the paths to the images and videos of my product?

Below the SQL of product table creation:

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  quantity INT NOT NULL,
  description VARCHAR(1000),
  price DECIMAL(7,2) NOT NULL,
  status INT NOT NULL,
  created DATETIME,
  modified DATETIME
);

The paths to:

• Pictures of the product
• Thumbnail (small descriptive photo of the product)
• Videos of the product

And how I differentiate each path (in case the user wants a certain photo or all photos)?

Answer 1

I would create two tables to store this information. Because it may be possible for a photo to apply to more than one product. In case that never happens you can simplify the model, but I would do so:

A generic table to store image or video information

create table Media 
(
     id   
     type        -- Tipo de media (imagem, thumbnail, video, word document, ...)
     filepath
     description -- meta data, para permitir busca por imagem por exemplo...
)

Another table to manage Product-Media relationships

create table ProductsMedia
(
   productID
   mediaID
)

This way it would be easy for you to filter the type of content depending on the user’s choice. If you want to see all type of content associated with a product no filter would be necessary. If you just want to see images then just filter through this particular content by doing for example:

select p.name,
       p.description,
       pprice,
       m.id
from products p
left join ProductsMedia pm
  on pm.productID = p.id
left join Media m
  on m.id = pm.mediaID
where m.type = 'foto'            -- apenas um exemplo
  and m.metadata like %gato%-- imagens em que apareça um gato