All geek questions in one place

The issue of designing a database of e-commerce applications

I am creating an e-commerce database ... I would like to suggest the design of the following case:

  • The product may have several sizes and several colors.
  • Each combination of product sizes / colors must have a specific price.

So, I can have product X with sizes: A, B, C and colors: Green, Black and White

And each combination has a price ...

It is implemented in Amazon, one example here: http://www.amazon.com/Champion-Mens-Jersey-Black-Large/dp/B0010EEYCU/ref=sr_1_4?ie=UTF8&s=sporting -Products & QID = 1287767784 & avg = 1-4

I need help developing this!

EDIT: a product may have a price without size and color

thank

+3
4

, :

  • Product . .
  • ( , , ), Size Colour PricePoint, - , PricePoint .

, , , /. , SKU ( ), :

  • A Product, , SKU Price .
  • A Size , . Product .
  • A Colour, .
  • A ProductType ProductGrouping , /. " ". , ProductType = 'T-Shirt', , - , .

: "superset" OP, @Phil Sandler :

:

Product Group (defines a superset of similar products that will be grouped or filtered together)
product_group_id
product_group_name

Product, :

Product Table (defines the product):
product_id
product_group_id
product_name

, , :

: product_id = 100, product_group_id = 1, product_name = ' '

: product_group_id = 1, product_group_name = 'T-Shirts'

: product_color_id = 10, product_id = 100, color_id = 6 , "6" - "". , Crew-neck T Blue

: product_size_id = 11, product_id = 100, size_id = 2 , "2" - "". , Crew-neck T Medium

: product_price_id = 555, product_id = 100, product_size_id = 11, product_color_id = 10, = 24.99 * , Medium Blue Crew-neck T 24,99 ( price_id, ) *

, , , " , ".

+7

, , . -, , // . , , !

, , . , , . , .

, .

:

-, (, , ..). .

-, (, , ​​, , ..). , , .

, 1 "" - "", "", "" .. .

, .

: , "optionsGroup", . , , , , "" "". , , , , "Hoodies". , - , .

, , . . , , . .

, , .

: , - SKU! , , , - , , , .

, ( !), , , , , .

+6

:

Product Table (defines the product):
product_id
product_name

Product Size Table (defined valid sizes for the product):
product_size_id
product_id
size_id (assuming here that you have a lookup table for generic sizes like S/M/L/XL)

Product Color Table (defines valid colors for the product):
product_color_id
product_id
color_id (again, assuming there is a lookup for Blue/Green/Purple/etc.)

Product Price Table (applies a price to the product/size/color combination):
product_price_id
product_size_id
product_color_id
price
+2

SKU . SKU , , - SKU.

create table product (
  product_id integer primary key,
  name varchar(255) not null,
  description varchar(2048) not null
);

create table sku (
  sku_id integer primary key,
  product_id integer references product,
  size varchar(40),
  colour varchar(40),
  price numeric(8, 2) not null,
);

, SKU , . , , , ..

SKU , - .

, -, , .

+2

Source: https://habr.com/ru/post/1770967/

More articles:


All Articles