Contact

E-Commerce Concepts (SKU, EAN, UAN...)

Hi friends, today E-commerce concepts is making it own area in business world. Every business-man wants their own tools to manage their products and selling items.

Business-man can sell their products online and offline. They need to manage their products by tracking the inventory and sales on each location i.e online and offline.

To successfully manage the product catalog we need to understand few important concepts  

  • SKU (Stock Keeping Unit)
  • UPC (Universal Product Code)
  • Product Attributes
  • Quantity
What is SKU?
    When we purchase any item we certainly sees  SKU  (Stock Keeping Unit) is a string which mostly but not always a alpha-numeric value represents the product for seller's. Their is no standard rules to maintain SKU, but it is maintained in such a way that employee can understand or categorize each product items only by looking at SKU number.
Example- SKU for same T-shirt and manufacturer but with different color/size will be slight different because of color/size variant.

T-ShirtNikeRed - SMALLTS-NK-SM-R
T-ShirtNikeBlue- SMALLTS-NK-SM-B
SKU is boud to the perticular location, so SKU of product at offline store will  be slight different for online store.


What is UPC (Universal Product Code)?

UPC is another identifying factor. It mainly consist of two parts, first is machine-readable barcode number and unique 12 digit product code. the purpose of UPC is to easily identifying the product features like brand, color, size,UOM. It speedup the selling process. And also helpful to track inventory.

Managing Product Attributes

The most important section of any product management part is managing their attributes. Their are different ways to manage this but i will try to describe here with managing problems.

Suppose a e-commerce postal want to sell T-shirt only, below design will suit for the database table

TS-S-C-RT-ShirtSmallRedCotton
TS-S-C-B
T-ShrtSmallBlueCotton
TS-M-S-W
T-ShirtMediumWhiteSilk
But what is they want to sell also Pants, This product does not requires size but waist and inseam, so need to add more column



P-JS-B-30-30Jeans Blue
3030
P-TR-G-36-34
Trusers Gray
3634
P-FP-W-34-35
Formal Pants White

3435
we can see db admin added new column to manage new products. Again looks good if only want sell these two products. 
But after some days/month seller wants to add more product like belt, shoes, saree etc
Now this model will be very hard to manage and will need re-design.

Another approach is add different table for different product. This will also create new issues and changes whenever seller want add new product and developer will require to change queries to get the product detail/attributes.

so what will be the standard and acceptable solution for this.

The solution is normalization of the tables to some context such that retrieval of the product should not require deeper joins and with also very less tables.

Product Table
IDSKUProduct_Name
1 TS-S-C-RT-Shirt
2TS-M-S-B
T-Shirt
3P-JS-B-30-30
Jeans
4S-M-B-9
Shoes

Product Attribute
IDProduct_IDAttribute_NameAttribute_Value
11SizeSmall
23Waist30
34Size9

This model can also be normalize to next level, but in e-commerce every product is different based upon their attributes, so in product table name is duplicated but SKU is different because their attributes are not same. 

Managing Inventory(Quanity)

Managing Inventory is also very important aspect for sellers. Each transaction should be managed either Sale, returns, purchase, or payment success, failed, cancel etc

Their are two known ways to show current items 
  • Calculate items for every transaction type
  • Manage Current inventory count on every transaction


QTHEM- Demonstrator

-

QTHEM-SALES TEAM

-

top mobile,android,ios developers

Picker App

top mobile,android,ios developers

Sales Application

Share us on

Get In Touch