只要看着鞋子，你就有一个实体：鞋子。它有两个直接的属性：大小和颜色。这些属性中的每一个的域都必须严格定义，这表示它们的查找表。有两个间接属性，价格和数量，但是这些属性是每种尺寸/颜色组合的属性，而不是鞋子本身。

这表示一个实体表： ;两个查找表：大小和颜色;和一个三向路口表：ShoeStyles：

 创建表ShoeStyles（
 ShoeID int not null，
 SizeID smallint not null，
 ColorID char（1）not null，
价格货币，
数量int不为null默认值0，
约束FK_ShoeStyles_Shoe外键引用Shoes（ID） 
约束FK_ShoeStyles_Size外键引用尺寸（ID），
约束FK_ShoeStyles_Color外键引用颜色（ID），
约束PK_ShoeStyles主键（ShoeID，SizeID，ColorID）
）; 
 

因此，例如，组合（'Penny Loafer'，'10 1/2'谭'）将有一个特定的价格和数量在手。尺寸11 Tan将有自己的价格和数量，以及10 1/2 Burgandy。

我会推荐一个视图，加入表，并提出结果在更可用的形式，如上所示，而不是例如（15,4,3,45.00,175）。视图上的触发器可以允许应用程序通过视图进行所有访问，因此应用程序保持对数据的物理布局的不可知性。这样的视图是一个非常强大的工具，显着增加了基础数据和应用程序本身的鲁棒性和可维护性，但是未被充分利用。

I have to design database for cloth and shoes ecommerce website,
I'm not sure I did proper or not for future postgresql querying usage?

Example The product could be like:

(name) a shoes >  (size) 36  > (color) red > (price) 100 > (qty) 2    
(name) a shoes >  (size) 37  > (color) red > (price) 300 > (qty) 4  
(name) a shoes >  (size) 38  > (color) red > (price) 500 > (qty) 4  

(name) b shoes >  (size) 36  > (color) green > (price) 200 > (qty) 6  
...  



(name) a top >  (size) xs  > (color) purple > (price) 300 > (qty) 2  
...  

(name) a pants >  (size) 100-120cm  > (color) pink > (price) 100 > (qty) 2  
...  
(name) b pants >  (size) s  > (color) pink > (price) 100 > (qty) 2  

The size not always sml or n-n cm... could be any the string from the item manufacturer so I leave column as input some text.

and I separate color (product_size_color) price (product_size_color_price) and quantity (product_size_color_price_quantity) because the website is multiple language so in future I have to creat another table like product_size_color_jp, product_size_color_price_jp ...

please any suggestion are welcome..

table: product_base

primary: 
product_id

column:
product_id SERIAL NOT NULL,
product_name varchar,
product_introduction varchar,
product_description varchar,
active bit NOT NULL,
create_by_user_id integer,
create_date timestamp,
modified_by_user_id integer,
modified_date timestamp

table: product_size

primary: 
product_size_id

column:
product_size_id SERIAL NOT NULL,
product_id integer NOT NULL, FOREIGN KEY (product_id) REFERENCES product_base (product_id) ON DELETE CASCADE
product_size_name varchar,
product_size_description varchar,
active bit NOT NULL,
create_by_user_id integer,
create_date timestamp,
modified_by_user_id integer,
modified_date timestamp

table: product_size_color

primary:
product_size_color_id

column:
product_size_color_id SERIAL NOT NULL,
product_size_id integer NOT NULL, FOREIGN KEY (product_size_id) REFERENCES product_size (product_size_id) ON DELETE CASCADE
product_size_color_rgb_code_r integer,
product_size_color_rgb_code_g integer,
product_size_color_rgb_code_b integer,
product_size_color_name varchar,
create_by_user_id integer,
create_date timestamp,
modified_by_user_id integer,
modified_date timestamp

table: product_size_color_price

primary: 
product_size_color_price_id

column:
product_size_color_price_id SERIAL NOT NULL,
product_size_color_id integer NOT NULL, FOREIGN KEY (product_size_color_id) REFERENCES product_size_color (product_size_color_id) ON DELETE CASCADE
product_size_color_price integer,
create_by_user_id integer,
create_date timestamp,
modified_by_user_id integer,
modified_date timestamp

table: product_size_color_price_quantity

primary: 
product_size_color_price_quantity_id

column:
product_size_color_price_quantity_id SERIAL NOT NULL,
product_size_color_price_id integer NOT NULL, FOREIGN KEY (product_size_color_price_id) REFERENCES product_size_color_price (product_size_color_price_id) ON DELETE CASCADE
product_size_color_price_quantity integer,
create_by_user_id integer,
create_date timestamp,
modified_by_user_id integer,
modified_date timestamp

UPDATE

table:
product_base
primary:
product_id
column:
product_id SERIAL NOT NULL,
name varchar,
introduction varchar,
description varchar,
size_name varchar,
size_description varchar,
active bit NOT NULL,
create_by_user_id integer,
create_date timestamp,
modified_by_user_id integer,
modified_date timestamp


table:
product_color
primary:
product_color_id
column:
product_color_id SERIAL NOT NULL,
product_id integer NOT NULL, FOREIGN KEY (product_id) REFERENCES product_base (product_id) ON DELETE CASCADE
color_rgb_code_r integer,
color_rgb_code_g integer,
color_rgb_code_b integer,
color_name varchar,
active bit NOT NULL,
create_by_user_id integer,
create_date timestamp,
modified_by_user_id integer,
modified_date timestamp


table:
product_color_price
primary:
product_color_price_id
column:
product_color_price_id SERIAL NOT NULL,
product_color_id integer NOT NULL, FOREIGN KEY (product_color_id) REFERENCES product_color (product_color_id) ON DELETE CASCADE
price integer,
create_by_user_id integer,
create_date timestamp,
modified_by_user_id integer,
modified_date timestamp


table:
product_color_quantity
primary:
product_color_quantity_id
column:
product_color_quantity_id SERIAL NOT NULL,
product_color_id integer NOT NULL, FOREIGN KEY (product_color_id) REFERENCES product_color (product_color_id) ON DELETE CASCADE
quantity integer,
create_by_user_id integer,
create_date timestamp,
modified_by_user_id integer,
modified_date timestamp

Just looking at shoes, you have one entity: shoes. It has two direct attributes: size and color. The domain of each of these attributes must be strictly defined, which indicates lookup tables for them. There are two indirect attributes, price and quantity, but these are attributes more of each combination of size/color than of a shoe itself.

This suggests one entity table: Shoes; two lookup tables: Sizes and Colors; and one three-way intersection table: ShoeStyles:

create table ShoeStyles(
    ShoeID   int       not null,
    SizeID   smallint  not null,
    ColorID  char( 1 ) not null,
    Price    currency,
    Qty      int       not null default 0,
    constraint FK_ShoeStyles_Shoe foreign key references Shoes( ID ),
    constraint FK_ShoeStyles_Size foreign key references Sizes( ID ),
    constraint FK_ShoeStyles_Color foreign key references Colors( ID ),
    constraint PK_ShoeStyles primary key( ShoeID, SizeID, ColorID )
);

Thus, for example, the combination ('Penny Loafer', '10 1/2', 'Tan') will have a particular price and quantity on hand. The size 11 Tan will have its own price and quantity as will the 10 1/2 Burgandy.

I would recommend a view that joins the tables and presents the results in a more usable form as shown above rather than, say, (15, 4, 3, 45.00, 175). Triggers on the view could allow all access by the application through the view so the app remains agnostic of the physical layout of the data. Such views are an extremely powerful tool which adds significantly to the robustness and maintainability of the underlying data and of the app itself, but which are woefully under-utilized.