Model "Product" table for multiple types of different products

Navigation
6

How to model the Product table of a database? 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 pertinent to each type of product: food (natural and industrialized) , bed / table / bath, clocks (and props like bracelets and necklaces), perfumery articles, electronics and home appliances and furniture.

This table should allow filtering by characteristics of each product, each feature is relevant to one type of product, so I should be able to filter furniture by type of wood and computers by amount of RAM, however 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 is following CakePHP naming conventions.

OBS: The DBMS is MySQL latest version.

EDIT:

Following guidelines obtained through the question: #

Types:

  • Drinks
    • Type of drink
    • Package Contents
    • Provider
    • SAC
  • Food
    • Expiration time (1 month, 2 weeks)
    • Type (industrialized, in natura, dehydrated, powdered)
    • Brand
    • Producer Region
    • Nutrition Facts
  • Furniture
    • Material (metal, wood)
    • height
    • width
    • depth
    • weight
asked by anonymous 14.07.2015 / 14:11

1 answer

6

I would do it as follows:

  • 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 characteristic for a product;

It may seem a bit long-winded, but there is a very consistent structuring. My suggestion for 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
);
    
14.07.2015 / 17:00
Zoom with fingers in app Where to call SaveChanges () when you have multiple repositories?