Frontstage - Database design

[Vince-Ku]

Feature

Design the table format in the database.

---

Board

erDiagram
    Board {
        int id "Unique identifier for the board (Primary Key, Auto-Increment)"
        string code "Code used to filter/query"        
        string image_url "URL of the board's image"
        string action_type "Type of the action, like Deep link, Universal link, Web link, Javascript execution... etc"
        string action "Action content"
        date created_date "Timestamp when the record was created"
        date updated_date "Timestamp when the record was last updated"
    }

Example Data:

id: 1
code: "product_side_bar"
image_url: "http://example.com/image.jpg"
action_type: "deep_link"
action: "orange://main_page"
created_date "2023-11-28 15:30:45"
updated_date "2023-11-28 15:30:45"

---

Theme

erDiagram
    Theme {
        int id "Unique identifier for the theme (Primary Key, Auto-Increment)"
        string type "Type of the theme"
        string code "Code used to filter/query"
        boolean disable "True if the Theme is active, False otherwise"
        date created_date "Timestamp when the record was created"
        date updated_date "Timestamp when the record was last updated"
    }

Example Data:

id: 1
type: "Explore"
code: "carousel_board"
disable : "true"
created_date "2023-11-28 15:30:45"
updated_date "2023-11-28 15:30:45"

---

Distributor

erDiagram
    Distributor {
        int id "Unique identifier for the distributor (Primary Key, Auto-Increment)"
        string name "Name of the distributor"
        string description "Description of the distributor"
        string brand_image "URL to the brand's image"
        date created_date "Timestamp when the record was created"
        date updated_date "Timestamp when the record was last updated"
    }

Example Data :

id: 1
name: "PCHome" 
description : "台灣電商龍頭"
brand_image : "http://example.com/image.jpg"
created_date "2023-11-28 15:30:45"
updated_date "2023-11-28 15:30:45"

---

Product_Priority

erDiagram

   Product_Priority {
        int distributor_id "Identifier for the associated distributor"
        string code "Code used to filter/query"
        int product_id  "Identifier for the associated product"
        int priority "Sequence"
        date created_date "Timestamp when the record was created"
        date updated_date "Timestamp when the record was last updated"
   } 

Example Data :

distributor_id: 1
code: "main_distributors"
product_id: 2
priority: 3
created_date "2023-11-28 15:30:45"
updated_date "2023-11-28 15:30:45"

---

User_Like_Distributor, User_Like_Distributor_Log

erDiagram
    User_Like_Distributor {
        int distributor_id "Identifier for the associated distributor"
        int user_id  "Identifier for the associated user"
        date created_date "Timestamp when the record was created"
    }

    User_Like_Distributor_Log {
        int distributor_id "Identifier for the associated distributor"
        int user_id  "Identifier for the associated user"
        boolean like "False if the distributor is liked, False otherwise"
        date created_date "Timestamp when the record was created"
    }

Example Data for User_Like_Distributor :

distributor_id: 2 
user_id: 456
created_date "2023-11-28 15:30:45"

Example Data for User_Like_Distributor_Log :

distributor_id: 2 
user_id: 456
like: true
created_date "2023-11-28 15:30:45"

---

APIs Reference

Board, Theme Distributor Product related

[Vince-Ku]

@asdfg429 if you are interested in this feature, welcome to join me.

[Vince-Ku]

@shadow3x3x3 These tables are designed according to the requirement from the Explore module in iOS, please help to review.

@KaoWeiCheng @asdfg429 please take a look if available, welcome to join me.

[asdfg429]

@Vince-Ku

1. Could upload a picture or something else to point out how you use these tables? 2 I'm not sure is good or not. If images will be saved in local machine, should us created a table for record these images? Like

   int id ,
   varchar category,-- theme/products/discount....etc,
   int referance_id, -- like products.id=1 than record 1 theme.id = 3 than record 3,
   int sort

   images location is {root}/{category}/{id}/{sort}.jpg (ex:products/2/3.jpg) but first we should put images at right dir LOL

[Vince-Ku]

@asdfg429

1. Could upload a picture or something else to point out how you use these tables?

Uploading pictures and making a description of the usage of APIs is a quite large effort, maybe we discuss this in the meeting this week? it would be more straightforward.

The requirements are based on those APIs we've discussed in the previous few meetings. Board, Theme Distrubutor

2 I'm not sure is good or not. If images will be saved in local machine, should us created a table for record these images? Like

int id ,
varchar category,-- theme/products/discount....etc,
int referance_id, -- like products.id=1 than record 1 theme.id = 3 than record 3,
int sort

images location is {root}/{category}/{id}/{sort}.jpg (ex:products/2/3.jpg) but first we should put images at right dir LOL

That sounds great, BTW, we could also consider setting up an independent server specifically to handle image requests.

[shadow3x3x3]

First, thanks for creating these visualized tables using Mermaid. This will be very helpful for discussions.

However, I still have some suggestions for your reference.

---

Board

Old	New
```mermaid erDiagram Board { int id string code string image_url string action date created_date date updated_date } ```	```mermaid erDiagram Board { int id string code string image_url string link_type string link date created_date date updated_date } ```

I separated the action into two columns, link_type and link.

• link_type: It used to indicate the type of the link, which can be deep link, universal link or web link
• link: The link value to the board. e.g. "orange://main_page"

Therefore, the multiple type of links can be stored in the same table.

---

Theme

I am OK with the theme table.

---

Distributor

I am OK with the Distributor table.

---

Product Sort

I recommend to use priority instead of sort as the table and column name.

---

Distributor_User_Like

Old	New
```mermaid erDiagram user_like_distributors { int id int distributor_id int user_id boolean like date created_date date updated_date } ```	```mermaid erDiagram user_like_distributors { int distributor_id int user_id date created_date } ```

I removed id, like, updated_date columns for the following reasons:

1. id: The primary key can be (distributor_id, user_id).
2. like: The existence of a record in the table means the distributor is liked by the user.
3. updated_date: The table only stores the creation date (like date) of the record.

btw, I fixed the table name to user_like_distributors.

How do you feel about this?

[Vince-Ku]

Board

Old New

erDiagram
    Board {
        int id
        string code
        string image_url
        string action 
        date created_date
        date updated_date
    }

erDiagram
    Board {
        int id
        string code
        string image_url 
        string link_type
        string link
        date created_date
        date updated_date
    }

I separated the action into two columns, link_type and link.

• link_type: It used to indicate the type of the link, which can be deep link, universal link or web link
• link: The link value to the board. e.q. "orange://main_page"

Therefore, the multiple type of links can be stored in the same table.

Separating into two columns is great. How about we retain the term action? (e.g. action_type, action) to ensure future extensibility, except for links, there are various behaviors that might happen in iOS, such as executing javascript, presenting alerts, and more.

Product Sort

I recommend to use priority instead of sort as the table and column name.

Sure.

Distributor_User_Like

Old New

erDiagram
    user_like_distributors {
        int id
        int distributor_id
        int user_id
        boolean like
        date created_date
        date updated_date
    }

erDiagram
    user_like_distributors {
        int distributor_id
        int user_id 
        date created_date
    }

I removed id, like, updated_date columns for the following reasons:

1. id: The primary key can be (distributor_id, user_id).

Agree.

2. like: The existence of a record in the table means the distributor is liked by the user.
3. updated_date: The table only stores the creation date (like date) of the record.

The reason that I retain the column like is only for the record, allowing us to potentially analyze user behavior in the future. Do you think so?

if we want to do so, would it be more practical to split into two tables user_like_distributors, user_like_distributors_log ? then user_like_distributors is able to delete the column like and updated_date.

btw, I fixed the table name to user_like_distributors.

How do you feel about this?

The like behavior is created by the user instead of the distributor, I think your naming is better !!

[shadow3x3x3]

Separating into two columns is great. How about we retain the term action? (e.g. action_type, action) to ensure future extensibility, except for links, there are various behaviors that might happen in iOS, such as executing javascript, presenting alerts, and more.

Sure. It makes sense.

The reason that I retain the column like is only for the record, allowing us to potentially analyze user behavior in the future. Do you think so?

if we want to do so, would it be more practical to split into two tables user_like_distributors, user_like_distributors_log ? then user_like_distributors is able to delete the column like and updated_date.

Yeah. Sepearating to Query and Log tables would be more KISS. Otherwise, there would be two situations can represent dislike, non-exists column and like is false.

[Vince-Ku]

All are updated, please check.

[shadow3x3x3]

All are updated, please check.

So far so good. 🚀

[Vince-Ku]

I'll keep this issue in the Review status until the next meeting.

@KaoWeiCheng @asdfg429 If you guys want to join the discussion or have any questions or suggestions, feel free to leave a message :D ~

[KaoWeiCheng]

@Vince-Ku The columns "created_at" and "updated_at" in the DB table "boards" correspond to "created_date" and "updated_date" mentioned in the documentation. My program currently prioritizes the table's columns, so if any changes are needed, please let me know. Thank you.