Let's learn Shopping Mall Architecture of Relational Database
Tags: database, tutorial, beginners, architecture
Published: 2024-01-16 17:24:23

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oisumivfy3ft6tcqrfmv.png)

## 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`](https://github.com/samchon/prisma-markdown)




## Articles
![Article Entities](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8oyr7gt9t7gq55v6c8jc.png)

[**Articles** section in the ERD document](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#articles)

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
![Sale Entities](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6wadu4w4xx9aoz79zxnf.png)

[**Sales** section in the ERD document](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#sales)

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

![Macbook](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pn41f1ak8dd6j39rsi5t.png)

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`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#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`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sale_snapshot_unit_options)). At the "CPU" option, ("M3", "M3 Pro" and "M3 Ultra") are the **Candidate** ([`shopping_sale_snapshot_unit_option_candidates`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#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`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sale_snapshot_unit_stocks)). Also, selection information of **Stock** is called **Choice** ([`shopping_sale_snapshot_unit_stock_choices`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#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`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sale_snapshot_unit_options) and [`choice`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sale_snapshot_unit_stock_choices) records. They just have one [stock](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sale_snapshot_unit_stocks) per one [unit](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sale_snapshot_units).

![A/B Testing](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nhekfmqvhu3lxi06veaj.png)

Also, in the previous article section, you've learned the concept snapshot. Here [`shopping_sales`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sales) has the same roled entity [`shopping_sale_snapshots`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sale_snapshots). Whenever seller updates the sale, new snapshot record being created and accumulated to the belonged sale. In here [`shopping_sales`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#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
![Cart Entities](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aujf782quin5k0y0skcw.png)

[**Carts** section in the ERD document](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#orders)

In the real world, when a customer wants to purchase some [stocks](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sale_snapshot_unit_stocks) of [sales](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_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`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_cart_commodity_stocks) entity. The attribute `quantity` of [`shopping_cart_commodity_stocks`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#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`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_cart_commodities) is matched with [`shopping_sale_snapshots`](https://github.com/samchon/prisma-markdown/blob/master/ERD#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`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_cart_commodities) entity.

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

Sale | Cart
-----|------
x    | [`shopping_carts`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_carts)
[`shopping_sale_snapshots`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sale_snapshots) | [`shopping_cart_commodities`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_cart_commodities)
[`shopping_sale_snapshot_unit_stocks`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sale_snapshot_unit_stocks) | [`shopping_cart_commodity_stocks`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_cart_commodity_stocks)


## Orders
![Order Entities](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6bemry5qhgigt2yr7wec.png)

[**Orders** section in the ERD document](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#orders)

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`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#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`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_order_goods). At last, confirming the order by paying cash is matched with the [`shopping_order_publishes`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_order_publishes) entity.

Sale | Cart | Order
-----|------|-------
x    | [`shopping_carts`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_carts) | [`shopping_orders`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_orders)
[`shopping_sale_snapshots`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sale_snapshots) | [`shopping_cart_commodities`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_cart_commodities) | [`shopping_order_goods`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_order_goods)
[`shopping_sale_snapshot_unit_stocks`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_sale_snapshot_unit_stocks) | [`shopping_cart_commodity_stocks`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#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`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_deliveries), [`shopping_delivery_pieces`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#shopping_delivery_pieces) and [`shopping_delivery_journeys`](https://github.com/samchon/prisma-markdown/blob/master/ERD.md#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](https://github.com/samchon/prisma-markdown/blob/master/ERD.md), 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.