postgresql database design for ecommerce

Question

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

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, Aug 05 '15 at 19:52
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, Aug 05 '15 at 20:14
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, Aug 05 '15 at 20:18
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, Aug 05 '15 at 20:31
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, Aug 06 '15 at 00:28
@CraigRinger Thanks for reply, what about the `product` `size` `color` `price` `quantity` problem, in this case should I chain like this, otherwise I can't find other way to get the same result — user1775888, Aug 06 '15 at 00:42
@philipxy Thanks for the description and ref!! oh it seems really no need chain like what i did, I can just combine them in one or two table ^^ — user1775888, Aug 06 '15 at 16:44

score 1 · Accepted Answer · answered Aug 06 '15 at 17:32

1

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.

answered Aug 06 '15 at 17:32

TommCatt

5,498
1
13
20

1

Minor point: Since this is Postgres, which will lowercase all unquoted identifiers, the OP's use of underscore_separation is more appropriate than your CamelCase examples. – IMSoP Aug 06 '15 at 18:30
1

Such "lookup" base tables might or might not be derivable from the given tables, hence redundant & unnecessary. Eg if `product_size_color_price_quantity`'s meaning is "we offer product *product_id* in size *size* and color *color* in quantity *quantity*" then lookup tables listing permitted values might or might not be needed, and in any case it is probably not a join of them. Of course in practice we do usually have such tables. But you are suggesting a solution that assumes things not in the (granted, under-specified) question. – philipxy Aug 06 '15 at 18:46
1

@IMSoP: This was a general question concerning modeling. Unless the question involves something specific to the DBMS, I write responses as generic as possible and write them to be more human- than machine-readable. Table names, column names, even data types may be changed to fit personal style, company best practices / naming conventions and quirks of the underlying system. These don't have to be cut&paste-able answers. – TommCatt Aug 06 '15 at 19:33
@philipxy: The four tables I mention are replacements for the tables given in the question, not additions. – TommCatt Aug 06 '15 at 19:36
Thanks for reply in your example code if I have product top and top/shirts and top/t-shirt and furniture / chair / sofa ... does that means I have to add multiple table like table TopShirtsStyle, TopTShirtStyle , FurnitureChairSofaStyle ... – user1775888 Aug 07 '15 at 21:55
1

No, it is much too cumbersome to be (re)creating tables whenever you get a new product or product line. You need an overall entity representing "things we sell" identified by something like an SKU value. Then have product-specific tables if needed, but have the overall structure already defined. Here (http://stackoverflow.com/questions/29591851/database-structure-design/29646267#29646267) is an answer to a similar question. – TommCatt Aug 08 '15 at 20:05
I know that you are suggesting replacements; my comment was re your tables vis a vis the (absent) spec. (Please reread in that light.) The create/modify columns aside: If names are CKs then ids are not necessary. If product, size and/or color name is a CK and description isn't then yes have "lookups"; but that's *not given*. Your own CK (product,size,color) *suffices* to "strictly define" their domains, so still no ids required. And for all we know the example data is in 5NF: we don't know that those 3 determine price and/or quantity; maybe we have old & new stock of blue XXL jerseys. – philipxy Aug 17 '15 at 06:55
@philipxy: as you suggest, minus specific details, we must make assumptions. Mine was less assumption and more suggestion. When an attribute has a rigidly confined domain, such as color and size, then I always suggest at least starting with lookup tables. Price, otoh, is not so confined. – TommCatt Aug 17 '15 at 18:56

score 1 · Answer 2 · edited May 23 '17 at 10:26

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).

Thanks so much for help! I change my table in my update , I thought even the `quantiy` can add into the same one table, and for querying if I want to select unique `name` product can use distinct — user1775888, Aug 06 '15 at 20:06
I read this support.microsoft.com/en-gb/kb/283878 if I have to follow Second Normal Form does that means the first table have to stop at `product size`and `color` should be another table and reference `prodcut size` , but the `price` and `quantity` is follow by `color` so I have to add another 2 table for `price` and `quantity` follow Third Normal Form . is this correct it seems back to chain to to third level . please see my update code in question — user1775888, Aug 06 '15 at 23:11
I find this answer http://stackoverflow.com/a/2120892/1775888 it seems like I have to separate them like first ? — user1775888, Aug 06 '15 at 23:26
1. That microsoft link is poor & informal. 2. Your data does *not* have the form of the stackoverflow link. That link's tables are about categories within categories. Your question is not. PS I have edited this answer. — philipxy, Aug 16 '15 at 14:04

postgresql database design for ecommerce

2 Answers2

Linked