How to store an array of colors in a database?-CodePudding

I need create this functionality, I'm creating a clone of nike.com, and I have a problem. How to create a product table with color options? How should I store this data in the database (PostgreSQL Sequelize)? I already created two tables: Product and Color, and now I want to link them so, when creating the product table, I can add the existing colors from the Color table.

  async create(req: Request & { files: any }, res: Response, next: NextFunction) {
    try {
      const { name, price, typeId, categoryId } = req.body
      const colors = [1, 2, 3]
      const { img } = req.files
      let fileName = uuidv4()   '.jpg' 
      img.mv(path.resolve(__dirname, '..', 'static', fileName))
      const product = await Product.create({
        name,
        price,
        img: fileName,
        typeId,
        categoryId,
        colors: //[2, 1] how to select from colorTable?
      })
      return res.json(product)
    } catch (e) {
      next(ApiError.BadRequest(e.message))
    }
  }

const Product = sequelize.define('product', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  img: { type: DataTypes.STRING, allowNull: false },
  name: { type: DataTypes.STRING, unique: true, allowNull: false },
  price: { type: DataTypes.INTEGER, allowNull: false },
  rating: { type: DataTypes.INTEGER, defaultValue: 0 },
})

const Color = sequelize.define('color', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  color: { type: DataTypes.STRING, unique: true, allowNull: false },
})


Color.belongsToMany(Product, { through: TypeColor })
Product.belongsToMany(Color, { through: TypeColor })

CodePudding user response：

I am not sure what you are looking for. if you want to select colour in Color table, why don't you select before create Product?

Because you use async, i think calling color in table with async(color data what you want) makes no problem!

like below!!

async create(req: Request & { files: any }, res: Response, next: NextFunction) {
    try {
      const { name, price, typeId, categoryId } = req.body
      const colors = [1, 2, 3]
      const { img } = req.files
      let fileName = uuidv4()   '.jpg' 
      img.mv(path.resolve(__dirname, '..', 'static', fileName))

     const color = await Color.findAll({
      where :{} // here goes with your conditions      
})
      const product = await Product.create({
        name,
        price,
        img: fileName,
        typeId,
        categoryId,
        colors: //[2, 1] how to select from colorTable?
      })
      return res.json(product)
    } catch (e) {
      next(ApiError.BadRequest(e.message))
    }
  }

CodePudding user response：

What you describe is a many-to-many (M:M) is database terms. A product has many colors and a color is used by many products. This is resolved by creating a third table (Product_Color). This table contains the ID of each of the base tables as its own PK and each as a FK to the appropriate table. I do not know Sequelize but in Postgres the table definitions something like.

create table products( 
             prod_id integer generated always as identity 
           , name    test 
           -- additional product atteibutes 
           , constraint products_pk
                        primary key(prod_id)           
           ); 

create table colors(
             color_id integer generated always as identity 
           , name     text 
           -- additional color attributs
           , constraint colors_pk
                        primary key(color_id)  
           ); 

          
-- Resolution table for M:M products:colors
create table product_colors(
             prod_id  integer 
           , color_id integer
           -- intersection attributes 
           , constraint products_colors_pk
                        primary key (prod_id,color_id)
           , constraint products_colors_2_products_fk
                        foreign key (prod_id) 
                        references products(prod_id)
           , constraint products_colors_2_colors_fk
                        foreign key (color_id) 
                        references colors(color_id)
           );

Do not store as an array. They are initially simple but attempting to do anything with them gets complicated quickly. Like listing colors by products. It violates 1NF form, see here and here.