Let's learn Shopping Mall Architecture of Relational Database

Outline

Let's learn Shopping Mall (E-Commerce) Architecture of the Relational Database. With this article, you will realize how to define the principle concepts of the Shopping Mall, and how to normalize those concepts and express them into the Relational Database Architecture.

Here's the ERD (Entity Relationship Diagram) and detailed descriptions for each table to learn. The ERD document would be a little bit long to read, but don't be afraid. I'll describe the main concepts of them in here article.

Also, if you repeat to reading the ERD about 10 times, I can sure that you would be a moderated developer about the Relational Database Architecture Designing. I know that my architecture is not perfect and even not validated in the real world (based on my imagination), but it would be more fun and educative if you're trying to enhance the architeture, or trying to entirly re-making to another architecture better than mine.

• ERD + Descriptions: https://github.com/samchon/prisma-markdown/blob/master/ERD.md
• Generated by prisma-markdown

Articles

Articles section in the ERD document

Above bbs_articles and bbs_comments entities are super type entities used in every articles and comments of here Shopping Mall Database architecture. And you can find that, the word snapshot is repeated in both article and comment entities.

The snapshot means a revision of article (or comment). When user writes an article, a bbs_articles and its dependent one bbs_article_snapshots records are newly created. Whenever user updates the article, a new bbs_article_snapshots record would be accumulated to the target article. This is the concept of the snapshot.

The reason why accumulating article (or comment) revision histories is to keeping the evidence. As you know, in the Shopping Mall system, customer buys something from the seller with paying the real money. If a dispute arises between such customer and seller, it can escalate into a legal battle.

In that case, if a seller (or customer) tries to manipulate the situation and deceive judge by editing his/her previous article or comment, how it would be? This is the reason why archiving every snapshots of articles and comments, so that preserving evidence in the Shopping Mall system.

Sales

Sales section in the ERD document

Let's assume that you're planning to buy a Macbook.

At 1st, you've to decide which options to choose. As you know, price of the Macbook would be extremely diffrent depending on which options do you choose. Of course, the remained stock quantity also being affected by the option determinants.

At second, you may determine whether to buy additional products or not. You can determine to purchase "Apple Care Plus" for insuraing damage accident. You also can choose "Magic Mouse" and "Magic Keyboard" for convenience.

• Macbook Body
  • CPU: M3 / M3 Pro / M3 Ultra
  • RAM: 8 / 16 / 32 / 64 GB
  • SSD: 128 / 256 / 512 GB
  • Size: 13 / 15 inches
  • Color: Gold / Gray / Silver
• Macbook Charger
• Apple Care Plus
• Masic Mouse
• Magic Keyboard
  • Color: White / Black
  • Type: 82 / 106 keys

In the Shopping Mall DB architecture, ("Macbook Body", "Macbook Charger", "Apple Care Plus", "Magic Mouse" and "Magic Keyboard") are matched with the Unit (shopping_sale_snapshot_units). As you can see from the Macbook case, Apple (or distributor) sellers multiple products at the time in one catalogue. Unit is the concept a product in the catalogue.

About the "Macbook Body", ("CPU", "RAM", "SSD", "Size", "Color") are matched with the Option (shopping_sale_snapshot_unit_options). At the "CPU" option, ("M3", "M3 Pro" and "M3 Ultra") are the Candidate (shopping_sale_snapshot_unit_option_candidates) in the Shopping Mall DB Architecture.

At last, when customer chooses every candidate values for every options, the final product would be specified, so that its price and remained quantity would be determined. It is called as the Stock (shopping_sale_snapshot_unit_stocks). Also, selection information of Stock is called Choice (shopping_sale_snapshot_unit_stock_choices).

"MacBook Charger", "Apple Care Plus" and "Magic Mouse" do not have any option. In that case, they actually do not have any option and choice records. They just have one stock per one unit.

Also, in the previous article section, you've learned the concept snapshot. Here shopping_sales has the same roled entity shopping_sale_snapshots. Whenever seller updates the sale, new snapshot record being created and accumulated to the belonged sale. In here shopping_sales entity, the snapshot has been designed to keep the integrity and A/B test supporting.

For reference, the integrity means just keeping the sale history. Let's imagine a situation that the snapshot concept does not exist. In that case, if a customer purchases from the sale, and seller updates it later with price changing. How it would be? It is the 1st reason of snapshot desiging to keep integrity.

Also, most sellers want to perform A/B testing in the market. Some sellers may perform the A/B testing with price changing. Some sellers may hope to process the content level A/B testing. To make them exact result of their revision, such snapshot concept is also required.

• If I increase the price, how my net income would be?
• If I change the description content of the product, how customers would reflect?
• If I rename the product and sale title, how many customers will read it

Carts

Carts section in the ERD document

In the real world, when a customer wants to purchase some stocks of sales, the customer may put the product into a shopping cart. When composing to the shopping cart, customer may put same stocks repeatedly.

In the Shopping Mall DB Architecture, the concept "putting stocks to the shopping cart" is matched with the shopping_cart_commodity_stocks entity. The attribute quantity of shopping_cart_commodity_stocks means that how many same stocks are put into the shopping cart repeatedly.

Also, in the shopping cart entities, shopping_cart_commodities is matched with shopping_sale_snapshots, who is the parent group of the stocks. When customer puts duplicated combination of the children stocks, it would be represented by volume attribute of the shopping_cart_commodities entity.

• Customer puts 3 duplicated set of stocks
  • MacBook Body x1
  • MacBook Charger x2

Sale	Cart
x	shopping_carts
shopping_sale_snapshots	shopping_cart_commodities
shopping_sale_snapshot_unit_stocks	shopping_cart_commodity_stocks

Orders

Orders section in the ERD document

In the real world, after composing the shopping cart, customer may head to the checkout counter. In the checkout counter, customer applies the order by putting cart commodities to counter clerk. After that, confirm the order by paying cash.

The Shopping Mall DB Architecture is not much different from the real world. At first, shopping_orders entity is matched with the concept; applying order by putting down the commodities in the shopping cart. Also, in the order side, the commodity is matched with the shopping_order_goods. At last, confirming the order by paying cash is matched with the shopping_order_publishes entity.

Sale	Cart	Order
x	shopping_carts	shopping_orders
shopping_sale_snapshots	shopping_cart_commodities	shopping_order_goods
shopping_sale_snapshot_unit_stocks	shopping_cart_commodity_stocks	X

By the way, online Shopping Mall has an additional entity that is different with the off-line market. It is the delivery process. In the Shopping Mall DB Architecture, the delivery process is separted with three entities; shopping_deliveries, shopping_delivery_pieces and shopping_delivery_journeys.

At first, the shopping_deliveries entity is the main entity of the delivery. The next entity shopping_delivery_pieces represents which stocks and how much quantities being delivered by the shopping_delivery. At last, shopping_delivery_journeys means each step of delivery process like "manufacturing" and "delivering".

For reference, relationship between shopping_deliveries and shopping_orders are not 1: 1, but M: N. This is because seller can perform combined delivering combined with multiple orders/stocks. For example, a customer did multiple order, then seller can deliver all of the orders' stocks at once. In contrary, delivering a stock with multiple shopping_deliveries records is also possible due to weight or volume issue.

The Others

Looking at the ERD Document, there're much more sections and entities than this article. However, if you understand the core logic and principle concepts argued in here article, the other sections and entities may not such difficult.

Let's study the other entities.

Conclusion

If you have any question about this architecture, then just ask me with reply.

By the way, please note that, this Shopping Mall DB Architecture may not enough perfect. Its designing and description stories are all come from my imagination. Please write a question reply considering the aspect.

Also, I'm making backend server program following this Shopping Mall DB Architecture. It would be published at next month. As I've tried to implement it with ideal TDD (Test Driven Development) way, hope many expectations.

I'll come back soon with the backend server program. Thanks for reading my article.