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

Question

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

Navigation

#1 by (3 votes)

0

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

Below is the SQL for creating the product table:

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:

Product photos
Thumbnail (small product description photo)
Product videos

And how do I differentiate each path (if the user wants a particular photo or all the photos)?

sql modelo-relacional

asked by anonymous 14.07.2015 / 18:50

1 answer

Summernote Plugin fullscreen modal problem Working with Converted Dates

score 3 · Accepted Answer

I would create two tables to store this information. Because it may be possible for a photo to apply to more than one product. If that does not happen you can simplify the template, but I would do it like this:

A generic table for saving 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 relations

create table ProductsMedia
(
   productID
   mediaID
)

In this way it would be easy for you to filter the content type depending on the user's choice. If you want to see all kind of content associated with a product no filter would be needed. If you just want to see images then just filter by that particular content, 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