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
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.
SKU is boud to the perticular location, so SKU of product at offline store will be slight different for online store.
|T-Shirt||Nike||Red - SMALL||TS-NK-SM-R|
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
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-FP-W-34-35||Formal Pants|| ||White||34||35|
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.
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 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