Model "Product" table for multiple different product types

Question

How to model the Table Product from a data bank? This table should store the following information: id, product name, quantity, price, status (if it is in stock, if it will be returned) and information relevant to each type of product: food (natural and industrialized), clothing, bed/table/bath, watches (and props such as bracelets and necklaces), perfumery, electronic and household appliances and furniture.

This table should allow filtering by product characteristics, each feature is relevant to a product type, so I should be able to filter furniture by wood type and computers by RAM quantity, but both characteristics belong to a single product type.

SQL that I’ve done:

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
);

*She’s following Cakephp’s naming conventions.

OBS: SGBD is Mysql latest version.

EDIT:

Following guidance obtained through the question: Question I can separate 8 types of products and separate the characteristics for each type, but how will I structure this in the form of Entity Relationship? *Shows few types because I was informed that the question was great.

Types:

• Booze
  • Drinking type
  • Contents of the package
  • Supplier
  • SAC
• Food
  • Shelf life (1 month, 2 weeks)
  • Type (industrialised, in natura, dehydrated, powder)
  • Brand
  • Producing region
  • Nutritional information
• Furniture
  • Material (metal, wood)
  • height
  • width
  • depth
  • heaviness

Answer 1

I’d do it this way:

• A table with the product category;
• A table with the characteristics that a product of a given category has;
• A table with the data of each feature for a product;

It may seem a little long, but it is a very consistent structure. My suggestion of fields:

CREATE TABLE product_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  created DATETIME,
  modified DATETIME
);

CREATE TABLE product_category_features (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_category_id INT,
  name VARCHAR(255) NOT NULL,
  value_type VARCHAR(255), -- Aqui é o tipo de valor que a variável pode receber
  created DATETIME,
  modified DATETIME
);

CREATE TABLE product_features (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  product_category_feature_id INT,
  value VARCHAR(255),
  created DATETIME,
  modified DATETIME
);