Моделирование вариантов продукции

Я пытался моделировать варианты продукта и думал, что мне может понадобиться использовать EAV. Я мог бы сделать это без EAV, но я обеспокоен тем, что мог что-то пропустить. Здесь моя конструкция:

enter image description here

Вот что я пытаюсь представить:

  • A product может иметь 0 или более product variants (например, продукт футболки может иметь размер и цветные варианты).
  • A product variant может иметь 1 или более product variant options (например, вариант размера может быть небольшим, средним, большим).
  • А SKU состоит из 1 или более product variant options (таблица product_variant_option_combination содержит все возможные комбинации `product_variant_options. Итак, если бы было 3 размера и 3 цвета, было бы 3 * 3 = 9 комбинаций - и каждая комбинация получит собственный SKU и цену).
  • A product может иметь 1 или более SKUs.

Если у продукта нет вариантов, просто игнорируйте product_variants, product_variant_options и product_variant_option_combinations.

Является ли этот дизайн звуком? У меня возникнут проблемы с запросом? Будет ли оно масштабироваться? Нормализовано?

ОБНОВЛЕНИЕ 1

@Edper:

Если продукт может иметь 0 или много (опционный режим) варианты продукта (например, размер, цвет и т.д.). Из этого следует, что варианты продукта могут также иметь 0 или много продуктов, имеющих этот вариант?

Я так не думаю. Возможно, что у продукта, такого как "футболка", может быть вариант "размер" , а другой продукт, такой как "штаны", также может иметь "размерный" вариант, но я думаю, что это всего лишь случайность. Нет необходимости делать "размер" только как одну запись, потому что "размер" может иметь другой контекст.

Продукты, с которыми я имею дело, сильно различаются, и они должны иметь похожие варианты.

ОБНОВЛЕНИЕ 2:

Вот пример того, как я вижу свои данные:

enter image description here

Я применил вариант Size и связанные с ним значения. Я хочу дать понять, что они не считаются дублирующимися данными. Вариант Size для трех продуктов - это просто случайность. Думаю, нет необходимости нормализовать это. Каждый продукт может иметь 0 или более вариантов - и они мне неизвестны. Я ожидаю "дубликатов" (хотя они на самом деле не дублируются, поскольку они всегда относятся к определенному продукту, поэтому вариант "Размер" виджета "1" отличается от варианта "Размер" Widget 2).

ОБНОВЛЕНИЕ 3:

Теперь я вижу, что в моем дизайне возможно, что product имеет несколько идентичных product_variants. Я думаю, что это можно решить, сделав product_variants. product_id и product_variants. name составной ключ. Это означает, что Widget 1 может иметь только вариант "Размер" .

product_variant_options. product_variant_id product_variant_options. name также должен быть составным.

ОБНОВЛЕНИЕ 4:

Обновив мой product_variant_option_combinations, включите product_variant_id (FK в product_variants. id) и соблюдая ограничение UNIQUE с помощью product_variant_option_combinations. sku_id и product_variant_option_combinations. product_variant_id, я думаю, что я смог предотвратить проблему наличия SKU, который является как "Малым", так и "Большим". Это правильно?

enter image description here

-- phpMyAdmin SQL Dump
-- version 4.1.14
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Jul 30, 2014 at 03:35 AM
-- Server version: 5.6.17
-- PHP Version: 5.5.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `mydb`
--

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `name`) VALUES
(1, 'Widget 1');

-- --------------------------------------------------------

--
-- Table structure for table `product_variants`
--

CREATE TABLE IF NOT EXISTS `product_variants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE_product_id_name` (`product_id`,`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `product_variants`
--

INSERT INTO `product_variants` (`id`, `product_id`, `name`) VALUES
(2, 1, 'Color'),
(1, 1, 'Size');

-- --------------------------------------------------------

--
-- Table structure for table `product_variant_options`
--

CREATE TABLE IF NOT EXISTS `product_variant_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_variant_id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE_product_variant_id_name` (`product_variant_id`,`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `product_variant_options`
--

INSERT INTO `product_variant_options` (`id`, `product_variant_id`, `name`) VALUES
(2, 1, 'Large'),
(1, 1, 'Small'),
(4, 2, 'Black'),
(3, 2, 'White');

-- --------------------------------------------------------

--
-- Table structure for table `skus`
--

CREATE TABLE IF NOT EXISTS `skus` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `sku` varchar(45) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `skus_product_id_products_id_idx` (`product_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `skus`
--

INSERT INTO `skus` (`id`, `product_id`, `sku`, `price`) VALUES
(1, 1, 'W1SSCW', '10.00'),
(2, 1, 'W1SSCB', '10.00'),
(3, 1, 'W1SLCW', '12.00'),
(4, 1, 'W1SLCB', '15.00');

-- --------------------------------------------------------

--
-- Table structure for table `skus_product_variant_options`
--

CREATE TABLE IF NOT EXISTS `skus_product_variant_options` (
  `sku_id` int(11) NOT NULL,
  `product_variant_id` int(11) NOT NULL,
  `product_variant_options_id` int(11) NOT NULL,
  PRIMARY KEY (`sku_id`,`product_variant_options_id`,`product_variant_id`),
  UNIQUE KEY `UNIQUE_sku_id_product_variant_id` (`sku_id`,`product_variant_id`),
  KEY `spvo_product_variant_options_id_pro_idx` (`product_variant_options_id`),
  KEY `spvo_product_variant_id_product_var_idx` (`product_variant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `skus_product_variant_options`
--

INSERT INTO `skus_product_variant_options` (`sku_id`, `product_variant_id`, `product_variant_options_id`) VALUES
(1, 1, 1),
(2, 1, 1),
(3, 1, 2),
(4, 1, 2),
(1, 2, 3),
(3, 2, 3),
(2, 2, 4),
(4, 2, 4);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `product_variants`
--
ALTER TABLE `product_variants`
  ADD CONSTRAINT `product_variants_product_id_products_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `product_variant_options`
--
ALTER TABLE `product_variant_options`
  ADD CONSTRAINT `product_variant_options_product_variant_id_product_variants_id` FOREIGN KEY (`product_variant_id`) REFERENCES `product_variants` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `skus`
--
ALTER TABLE `skus`
  ADD CONSTRAINT `skus_product_id_products_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `skus_product_variant_options`
--
ALTER TABLE `skus_product_variant_options`
  ADD CONSTRAINT `skus_product_variant_options_sku_id_skus_id` FOREIGN KEY (`sku_id`) REFERENCES `skus` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `spvo_product_variant_options_id_product_variant_options_id` FOREIGN KEY (`product_variant_options_id`) REFERENCES `product_variant_options` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `spvo_product_variant_id_product_variants_id` FOREIGN KEY (`product_variant_id`) REFERENCES `product_variants` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;

Ответы

Ответ 1

У вас может быть такой дизайн:

 +---------------+     +-------------------+
 | PRODUCTS      |-----< PRODUCT_VARIANTS  |
 +---------------+     +-------------------+
 | #product_id   |     | #product_id       |
 |  product_name |     | #variant_id       |
 +---------------+     |  sku_id           |
         |             +-------------------+
         |                       |
+--------^--------+     +--------^--------+
| PRODUCT_OPTIONS |-----< VARIANT_VALUES  |
+-----------------+     +-----------------+
| #product_id     |     | #product_id     |
| #option_id      |     | #variant_id     |
+--------v--------+     | #option_id      |
         |              |  value_id       |
+-----------------+     +--------v--------+
| OPTIONS         |              |
+-----------------+              |
| #option_id      |              |
|  option_name    |              |
+-----------------+              |
         |                       |
 +-------^-------+               |
 | OPTION_VALUES |---------------+
 +---------------+
 | #option_id    |
 | #value_id     |
 |  value_name   |
 +---------------+

С основными, уникальными и внешними ключами:

  • ПРОДУКТЫ
    • PK: product_id
    • UK: product_name
  • OPTIONS
    • PK: option_id
    • UK: option_name
  • OPTION_VALUES
    • PK: option_id, value_id
    • UK: option_id, value_name
    • FK: option_id ССЫЛКИ ОПЦИИ (option_id)
  • PRODUCT_OPTIONS
    • PK: product_id, option_id
    • FK: product_id ССЫЛКИ ПРОДУКТЫ (product_id)
    • FK: option_id ССЫЛКИ ОПЦИИ (option_id)
  • PRODUCT_VARIANTS
    • PK: product_id, variant_id
    • Великобритания: sku_id
    • FK: product_id ССЫЛКИ ПРОДУКТЫ (product_id)
  • VARIANT_VALUES
    • PK: product_id, variant_id, option_id
    • FK: product_id, variant_id ССЫЛКИ PRODUCT_VARIANTS (product_id, variant_id)
    • FK: product_id, option_id ССЫЛКИ PRODUCT_OPTIONS (product_id, option_id)
    • FK: option_id, value_id ССЫЛКИ OPTION_VALUES (option_id, value_Id)

У вас есть:

  • ПРОДУКТЫ, например. Рубашка, Джемпер, Брюки.
  • ВАРИАНТЫ, например. Размер, цвет, длина
  • OPTION_VALUES, например Size: Small, Medium, Large; Цвет - красный, белый, синий.
  • PRODUCT_OPTIONS, например. Рубашка - размер, цвет; Брюки - длина, цвет.

Затем вам нужно создать n-мерный массив с количеством измерений, равным числу опций для продукта. Каждый элемент в массиве соответствует варианту продукта. Для каждого продукта всегда будет по крайней мере один вариант продукта; поскольку всегда существует псевдо-вариант продукта "как есть"

  • PRODUCT_VARIANTS, например, рубашка 1, рубашка 2
  • VARIANT_VALUES, например, рубашка 1: маленький красный; Рубашка 2: Маленький белый.

Возможно, вы захотите проверить, чтобы SKU не был назначен, если для всех параметров, связанных с продуктом, не были указаны значения.

В соответствии с таблицей того, как вы видите свои данные, вы можете ввести данные в свои таблицы следующим образом:

PRODUCTS
========
id  name
--- --------
1   Widget 1
2   Widget 2
3   Widget 3

PRODUCT_VARIANTS
================
id  product_id name
--- ---------- ------
1   1          Size   (Widget 1)
2   1          Color  (Widget 1)
3   2          Size   (Widget 2)
4   3          Class  (Widget 3)
5   3          Size   (Widget 3)

PRODUCT_VARIANT_OPTIONS
=======================
id  product_variant_id name
--- ------------------ -------------
1   1                  Small         (Widget 1; Size)
2   1                  Large         (Widget 1; Size)
3   2                  White         (Widget 1; Color)
4   2                  Black         (Widget 1; Color)
5   3                  Small         (Widget 2; Size)
6   3                  Medium        (Widget 2; Size)
7   4                  Amateur       (Widget 3; Class)
8   4                  Professional  (Widget 3; Class)
9   5                  Medium        (Widget 3; Size)
10  5                  Large         (Widget 3; Size)

SKUS
====
id  product_id sku    price
--- ---------- ------ -----
1   1          W1SSCW    10 (Widget 1)
2   1          W1SSCB    10 (Widget 1)
3   1          W1SLCW    12 (Widget 1)
4   1          W1SLCB    15 (Widget 1)
5   2          W2SS     100 (Widget 2)
6   2          W2SM     100 (Widget 2)
7   3          W3CASM    50 (Widget 3)
8   3          W3CASL    50 (Widget 3)
9   3          W3CPSM   150 (Widget 3)
10  3          W3CPSL   160 (Widget 3)

PRODUCT_VARIANT_OPTION_COMBINATIONS
===================================
product_variant_option_id sku_id
------------------------- ------
1                         1      (W1SSCW; Size; Small)
3                         1      (W1SSCW; Color; White)
1                         2      (W1SSCB; Size; Small)
4                         2      (W1SSCB; Color; Black)
2                         3      (W1SLCW; Size; Large)
3                         3      (W1SLCW;  Color; White)
2                         4      (W1SLCB; Size; Large)
4                         4      (W1SLCB; Color; Black)
5                         5      (W2SS; Size; Small)
6                         6      (W2SM; Size; Medium)
7                         7      (W3CASM; Class; Amateur)
9                         7      (W3CASM; Size; Medium)
7                         8      (W3CASL; Class; Amateur)
10                        8      (W3CASL; Size; Large)
8                         9      (W3CPSM; Class; Professional)
9                         9      (W3CPSM; Size; Medium)
8                         10     (W3CPSL; Class; Professional)
10                        10     (W3CPSL; Size; Large)

Кажется, что в вашем проекте нет ничего, что могло бы остановить добавление записи записи (product_variant_option_id: 2; sku_id 1), так что SKU W1SSCW теперь имеет обе опции Small и Large. Нет ничего, чтобы остановить запись записи (product_variant_option_id: 7; sku_id: 1), чтобы SKU W1SSCW также имел возможность любителя.

В соответствии с таблицей того, как вы видите свои данные, вы можете ввести данные в мои таблицы следующим образом:

PRODUCTS
========
product_id product_name
---------- ------------
1          Widget 1
2          Widget 2
3          Widget 3

OPTIONS
=======
option_id option_name
--------- -----------
1         Size SL
2         Color
3         Size SM
4         Class
5         Size ML

OPTION_VALUES
=============
option_id value_id value_name
--------- -------- ------------
1         1        Small        (Size SL)
1         2        Large        (Size SL)
2         1        White        (Color)
2         2        Black        (Color)
3         1        Small        (Size SM)
3         2        Medium       (Size SM)
4         1        Amateur      (Class)
4         2        Professional (Class)
5         1        Medium       (Size ML)
5         2        Large        (Size ML)

PRODUCT_OPTIONS
===============
product_id option_id
---------- ---------
1          1         (Widget 1; Size SL)
1          2         (Widget 1; Color)
2          3         (Widget 2; Size SM)
3          4         (Widget 3; Class)
3          5         (Widget 4; Size ML)

PRODUCT_VARIANTS
================
product_id variant_id sku_id
---------- ---------- ------
1          1          W1SSCW (Widget 1)
1          2          W1SSCB (Widget 1)
1          3          W1SLCW (Widget 1)
1          4          W1SLCB (Widget 1)
2          1          W2SS   (Widget 2)
2          2          W2SM   (Widget 2)
3          1          W3CASM (Widget 3)
3          2          W3CASL (Widget 3)
3          3          W3CPSM (Widget 3)
3          4          W3CPSL (Widget 3)

VARIANT_VALUES
==============
product_id variant_id option_id value_id
---------- ---------- --------- --------
1          1          1         1        (W1SSCW; Size SL; Small)
1          1          2         1        (W1SSCW; Color; White)
1          2          1         1        (W1SSCB; Size SL; Small)
1          2          2         2        (W1SSCB; Color; Black)
1          3          1         2        (W1SLCW; Size SL; Large)
1          3          2         1        (W1SLCW; Color; White)
1          4          1         2        (W1SLCB; Size SL; Large)
1          4          2         2        (W1SLCB; Color; Black)
2          1          3         1        (W2SS; Size SM; Small)
2          2          3         2        (W2SM; Size SM; Medium)
3          1          4         1        (W3CASM; Class; Amateur)
3          1          5         1        (W3CASM; Size ML; Medium)
3          2          4         1        (W3CASL; Class; Amateur)
3          2          5         2        (W3CASL; Size ML; Large)
3          3          4         2        (W3CPSM; Class; Professional)
3          3          5         1        (W3CPSM; Size ML; Medium)
3          4          4         2        (W3CPSL; Class; Professional)
3          4          5         2        (W3CPSL; Size ML; Large)

В моем проекте вы не смогли ввести дополнительную запись VARIANT_VALUES (product_id: 1; variant_id: 1; option_id: 1; value_id: 2) - так что SKU W1SSCW теперь имеет как опции Small и Large - из-за первичной ключ на VARIANT_VALUES и существующую запись VARIANT_VALUES (product_id: 1; variant_id: 1; option_id: 1; value_id: 1). В моем дизайне вы не смогли ввести запись VARIANT_VALUES (product_id: 1; variant_id: 1; option_id: 4; value_id: 1) - так что SKU W1SSCW также имеет возможность любителя - из-за внешнего ключа, ссылающегося на PRODUCT_OPTIONS, и отсутствия запись в этой таблице (product_id: 1; option_id: 4), указывающая, что класс является допустимым вариантом для продукта Widget 1.

EDIT: дизайн без таблицы PRODUCT_OPTIONS

У вас может быть такой дизайн:

+---------------+     +---------------+
| PRODUCTS      |-----< PRODUCT_SKUS  |
+---------------+     +---------------+
| #product_id   |     | #product_id   |
|  product_name |     | #sku_id       |
+---------------+     |  sku          |
        |             |  price        |
        |             +---------------+
        |                     |
+-------^-------+      +------^------+
| OPTIONS       |------< SKU_VALUES  |
+---------------+      +-------------+
| #product_id   |      | #product_id |
| #option_id    |      | #sku_id     |
|  option_name  |      | #option_id  |
+---------------+      |  value_id   |
        |              +------v------+
+-------^-------+             |
| OPTION_VALUES |-------------+
+---------------+
| #product_id   |
| #option_id    |
| #value_id     |
|  value_name   |
+---------------+

С основными, уникальными и внешними ключами:

  • ПРОДУКТЫ
    • PK: product_id
    • UK: product_name
  • OPTIONS
    • PK: product_id, option_id
    • UK: product_id, option_name
  • OPTION_VALUES
    • PK: product_id, option_id, value_id
    • UK: product_id, option_id, value_name
    • FK: product-id, option_id СПРАВОЧНЫЕ ОПЦИИ (product_id, option_id)
  • PRODUCT_SKUS
    • PK: product_id, sku_id
    • Великобритания: sku_id
    • FK: product_id ССЫЛКИ ПРОДУКТЫ (product_id)
  • SKU_VALUES
    • PK: product_id, sku_id, option_id
    • FK: product_id, sku_id ССЫЛКИ PRODUCT_SKUS (product_id, sku_id)
    • FK: product_id, option_id СПРАВОЧНЫЕ ОПЦИИ (product_id, option_id)
    • FK: product_id, option_id, value_id ССЫЛКИ OPTION_VALUES (product_id, option_id, value_id)

В соответствии с таблицей того, как вы видите свои данные, вы можете ввести данные в эти таблицы следующим образом:

PRODUCTS
========
product_id product_name
---------- ------------
1          Widget 1
2          Widget 2
3          Widget 3

OPTIONS
=======
product_id option_id option_name
---------- --------- -----------
1          1         Size        (Widget 1)
1          2         Color       (Widget 1)
2          1         Size        (Widget 2)
3          1         Class       (Widget 3)
3          2         Size        (Widget 3)

OPTION_VALUES
=============
product_id option_id value_id value_name
---------- --------- -------- ------------
1          1         1        Small        (Widget1; Size)
1          1         2        Large        (Widget1; Size)
1          2         1        White        (Widget1; Color)
1          2         2        Black        (Widget1; Color)
2          1         1        Small        (Widget2; Size)
2          1         2        Medium       (Widget2; Size)
3          1         1        Amateur      (Widget3; Class)
3          1         2        Professional (Widget3; Class)
3          2         1        Medium       (Widget3; Size)
3          2         2        Large        (Widget3; Size)

PRODUCT_SKUS
============
product_id sku_id sku
---------- ------ ------
1          1      W1SSCW (Widget 1)
1          2      W1SSCB (Widget 1)
1          3      W1SLCW (Widget 1)
1          4      W1SLCB (Widget 1)
2          1      W2SS   (Widget 2)
2          2      W2SM   (Widget 2)
3          1      W3CASM (Widget 3)
3          2      W3CASL (Widget 3)
3          3      W3CPSM (Widget 3)
3          4      W3CPSL (Widget 3)

SKU_VALUES
==========
product_id sku_id option_id value_id
---------- ------ --------- --------
1          1      1         1        (W1SSCW; Size; Small)
1          1      2         1        (W1SSCW; Color; White)
1          2      1         1        (W1SSCB; Size; Small)
1          2      2         2        (W1SSCB; Color; Black)
1          3      1         2        (W1SLCW; Size; Large)
1          3      2         1        (W1SLCW; Color; White)
1          4      1         2        (W1SLCB; Size; Large)
1          4      2         2        (W1SLCB; Color; Black)
2          1      1         1        (W2SS; Size; Small)
2          2      1         2        (W2SM; Size; Medium)
3          1      1         1        (W3CASM; Class; Amateur)
3          1      2         1        (W3CASM; Size; Medium)
3          2      1         1        (W3CASL; Class; Amateur)
3          2      2         2        (W3CASL; Size; Large)
3          3      1         2        (W3CPSM; Class; Professional)
3          3      2         1        (W3CPSM; Size; Medium)
3          4      1         2        (W3CPSL; Class; Professional)
3          4      2         2        (W3CPSL; Size; Large)