Post reply

Design a product table that allows me to sell almost any product from a pants, pharmacy products to a service

  • March 1, 2019 at 7:24 am

    #381567

    I have problems with a database design exactly in products, the problem is that I designed the database to sell products but through a Catalog but when I implemented I had many problems:

    You can not search for a product directly in the Products table if you do not go to Catalog to look for it and if it is not in Catalog it can not be sold.
    Another point is that my ProductCatalog table has specifications and that's where I got the problem since I had a Specifications table that was related to Options and the issue is that when separating sizes and colors from the Options table it is chaos.
    This is the complex query that I still have and I hope re - designing a more transparent query can be made.

    SELECT DISTINCT T.ProductoCatalogoId AS Id, T.ProductoId,
    CONCAT(T.Nombre, ' | ', T.TALLA, ' | ', C.COLOR) AS Descripcion, T.PrecioVenta, T.CATALOGO AS Nombre
    FROM
    (
      SELECT PC.ProductoCatalogoId, PC.ProductoId, P.Nombre, O.NombreOpcion AS TALLA, PC.PrecioVenta, C.Nombre AS CATALOGO
      FROM Productos AS P
      JOIN ProductoCatalogos AS PC ON P.ProductoId = PC.ProductoId
      JOIN EspecificacionCatalogos AS EC ON PC.ProductoCatalogoId = EC.ProductoCatalogoId
      JOIN Opciones AS O ON EC.OpcionId = O.OpcionId
      JOIN Especificaciones AS E ON EC.EspecificacionId = E.EspecificacionId
      JOIN Catalogos AS C ON PC.CatalogoId = C.CatalogoId
      WHERE P.Nombre LIKE '%VESTIDO PIQUE%' AND EC.EspecificacionId = 3
    ) AS T
    CROSS JOIN
    (
      SELECT PC.ProductoCatalogoId, PC.ProductoId, P.Nombre, O.NombreOpcion AS COLOR, PC.PrecioVenta, C.Nombre AS CATALOGO
      FROM Productos AS P
      JOIN ProductoCatalogos AS PC ON P.ProductoId = PC.ProductoId
      JOIN EspecificacionCatalogos AS EC ON PC.ProductoCatalogoId = EC.ProductoCatalogoId
      JOIN Opciones AS O ON EC.OpcionId = O.OpcionId
      JOIN Especificaciones AS E ON EC.EspecificacionId = E.EspecificacionId
      JOIN Catalogos AS C ON PC.CatalogoId = C.CatalogoId
      WHERE P.Nombre LIKE '%VESTIDO PIQUE%' AND EC.EspecificacionId = 2
    ) AS C

    Les muestro la imagen del diseño que les hablo para que tengan una mejor idea.

    Re - designing the database I have come to this solution in which you should look for the product directly from the table Products without intermediaries, I show you the new design.

    My current problem is to get the data in a transparent way of the details table when I see products that do have them, for example: A pair of pants that has:

    Size 28, 30, 32, 34
    Color Black, Blue, White
    When looking for a product I want to get the following result: Classic Lee 34 Blue Pant.

    The need that I have is that this design allows me to sell almost any type of product from clothes, pharmacy products to a service.

    I am not an expert in this matter if you can help me improve this design, the design is open to changes.

    Advance thanks!

  • March 6, 2019 at 2:48 pm

    #2023732

    For product tables that can encompass a wide variety of products, I like the EAV pattern because the data entry folks can create new product attributes without having to make schema changes.

    2 cents!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply