Design DB Schema

[eugenesimakin]

Washubrnfg85:

Basic thoughts:

1. There should be two tables: Users and Links.
2. Relationships between them should be like one-to-many. One user can own many links. Link shouldn't have any limitations or additional fields to store data about owners.

---

eugenesimakin:

Think about whole picture. You need to have:

• authentication info: email, password and username (why do we need the username? where do we use it?)
• profile details: first name, last name and email (could be different from the email above)
• links

Any ideas?

You could use a database schema diagrams service to illustrate it. For example, https://dbdiagram.io/home

---

Washubrnfg85:

https://dbdiagram.io/e/65722e0c56d8064ca09be431/65722f9956d8064ca09bf531

[eugenesimakin]

@Washubrnfg85 db schema needs to be improved (before we move to implementation).

Notes/Questions:

• Clarify relation between profile_details and links tables
• Where do you store user profile picture?

[Washubrnfg85]

Have some questions: Is login_email final? May it be replaced in future? It depends on user_email field in profile_details? When person register for the first time, profile initialises and login_email duplicates to profile_details.user_email and can be modified? Or they are totally different instances?

[eugenesimakin]

I suggest we make two tables: "users" and "user_profiles". Table "users" contains auth information and user primary id (email). Table "user_profiles" contains all profile details. Tables have one-to-one relationship on user primary id. Links for each user could be stored in one cell as JSON string (this is flexible and reasonable).

Is login_email final? May it be replaced in future?

Are you asking about naming? You could call it email (auth) and public_email (profile details).

It depends on user_email field in profile_details?

email (auth) is primary id and email (in the table "profile_details") is foreign id.

When person register for the first time, profile initialises and login_email duplicates to profile_details.user_email and can be modified?

We could do that or leave it empty.

Or they are totally different instances?

They might be different emails.

[eugenesimakin]

• [x] Create a diagram for DB schema (optional)
• [x] Make a PR with @Entity classes based on schema

[Washubrnfg85]

Got it. Just now making research on DB relations. Think that article should be useful: https://habr.com/ru/articles/488054/

[Washubrnfg85]

Links for each user could be stored in one cell as JSON string (this is flexible and reasonable).

So it sort of very long string with special characters, which we use to devide one link from another? To present links in UI we'll create spetial method to make list of links from that JSON string? Same list is usefull for sharing links.

[Washubrnfg85]

(why do we need the username? where do we use it?

I couldn't think of anything but personal appeal. "Hello, username!" or something.

[Washubrnfg85]

Take a look

https://dbdiagram.io/e/65722e0c56d8064ca09be431/65722f9956d8064ca09bf531

[Washubrnfg85]

The email thing.... We use login_emai for loging (obviously). I see good point in it. But why the heck we need user_email in profile?

[eugenesimakin]

But why the heck we need user_email in profile?

Take a look at this page of another link-sharing app - https://link-sharing-app.fly.dev/profile

[Washubrnfg85]

Ok. We'll use this email to distribute adds and spam. Mooahaha.