postgresql database design for ecommerce

1
votes

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
2
databaseperformancepostgresqldatabase-design
There is no need to chain the tables, this will just complicate things. And if there are translations, don't create new tables for them. – Sami Kuhmonen
thanks for reply!! 1 chain: so you suggest I should not use fk? and that means if I delete 1 product I have to make another 4 query to delete other related table? 2 translation: something like this product_name_jp varchar, product_name_ch varchar, ...? add column and accept null , if this way can I add more column? (for more language – user1775888
No, you should use foreign keys, but there is no point in chaining the tables. What you want is a product that has color, size and price, not a product that has a size and that size has a color and that color has a price. – Sami Kuhmonen
because the size is not fixed , different product have different size standard like the example, also different color chould be different price and qty , I only find I dont have to chain the price > qty, but above color < size < base have to. – user1775888
For different languages, consider having a table for translations. So the product data stays the same, and then there's a single table with translations, keyed by (product_id, language). All translations for diff. languages are in the same table. Partial indexes can be used for language-aware fulltext search. – Craig Ringer

2 Answers

1
votes

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.

1
votes

Your "chaining" is strange. You seem to have some misconception. What do you think is the reason why a table exists? Or an id attribute? You don't seem to understand the basics of relational design: Every base table holds rows that satisfy some particular parameterized statement about the application situation in terms of its columns, which is called a predicate. A query asks for rows that satisfy its own predicate, which is a combination of conditions and base table predicates. We choose base base table predicates sufficient to describe application situations. See this answer's section "Educate yourself about database design".

You need base/_base tables for each of product, size and colour, giving information particular to particular things. You do not need ids when names are unique per table. Although you might want ids as unique identifiers by people in the system that your database represents.

Next you probably only need to have a table with product, size, color, price and quantity columns. Not your product_size_color_price_quantity table with ids for longer and longer sets of columns. But you have not explained clearly exactly when a row goes into or stays out of that table. Ie given its predicate. And you have not explained clearly exactly what consequent restrictions apply to that table considering its predicate and what application situations can arise. Your example data does not give enough information.

Whether we instead split up the 5-way table depends on its predicate and its restrictions. Eg if price depends only on product then you should replace the new product_size_color_price_quantity table by product_size_color_quantity & product_id_price. This idea of replacing tables with statements like "... AND ..." by two or more each with a statement for a "..." is called normalization. The primary table restrictions that we need to know to normalize are called functional dependencies and join dependencies. You need to learn about normalization and other principles of design. If you give us the dependencies, we can tell you reasonable designs. (Normalization never introduces new column names.)

You haven't explained the role of your logging information so we can't say much about how it should be in your design.

For translations you need a table (or more depending on how much you use hardwiring vs metadata) for attribute x value x language x translation (with appropriate integrity constraints involving it & tables with those attributes).