Learn More
Resend Email
1
b2plane
151d

Profile (1, 1) --- (1, 1) User

Right?

- A single user *must* have *exactly* 1 profile.
- A single profile *must* belong to *exactly* 1 user.

Makes sense?

I did this because i moved user profile image and user banner image into Profile entity

So now i can easily join tables and fetch user profile image based on username or user ID

By deeply thinking like an asshole and overengineering, i stumbled upon a confusion

If i can join tables and get ALL fields (assuming its a left or full outer join) from both entities...

What is the difference between choosing which entity to fetch on the frontend?

For example if i want to fetch users, inversely, i can fetch Profile entity, which has User entity as a nested object, and that way access users. Now i have access to each user's profile image, banner image, bio etc aside from the entire user object

If the user navigates to a profile page, inversely, i can fetch User entity which will have a Profile entity as a nested object, and that way show the remaining necessary fields that the profile page needs to show

I gave these inverse examples because if i want to fetch users, surely enough i can simply fetch from User entity, and if i want to fetch someones profile data i can fetch from Profile entity directly

So if this is the case, when am i supposed to fetch one over the other?

You tell me. For simplicity lets focus on these two examples. Consider this as an exam question:

1) user navigates to home page. Now paginated users with role X need to be shown, but also their profile image. Do you fetch from User or Profile entity? If you use joins which ones and why?

2) user navigates to their or someone elses profile page. Now profile-based data needs to be shown, but also the user's username and full name need to be shown. Do you fetch from User or Profile entity? If you use joins which ones and why?

rant
Favorite
Ranter
Comments
  • 2
    151d
    Isn't the user and profile the same thing?
  • 0
    151d
    @iiii debatable. It may or may not be. The system is complicated. In this case there are 4 user roles, but only 1 can have a visible profile for public viewing. Other types of users do not need to have their own profile because they're either employees or consumers of the system.

    This is why i have separated User and Profile entity, so i can fill up Profile entity with data related only to that 1 User role who is allowed to have their own profile. If i merge them together then there will be some records that are ALWAYS gonna be null because of the other 3 roles which are NEVER gonna have their own user profile

    Its a weird system but it needs to work this way. understand better now?
  • 6
    151d
    then your user has 0,1 profiles, and a profile 1,1 users.
    thats a difference.

    since user is always present use user primarily. use profile when profile content should be displayed.
  • 2
    151d
    If profile only adds some extra fields and not to many, adding the extra table, while it avoids some null values, is probably more resource expensive than having some columns that is null for some users, especially if the users without a profile are a minority of the users which I guess they are.

    Normalization is often good but I found out the hard way that it is not always the best solution performance vice.

    Bit I do agree that since you always have users, treat that as the primary table, its should reduce the number of index you need and make it easier to reuse queries.
  • 0
    151d
    @kleopi

    Just curious what do you think of this query

    When you open whatsapp or any chat app the first thing you see is a list of all chats with people that youve had. You see their names and you also see the last message that was sent in a conversation.

    With this query i fetch the last chat message that was sent in a conversation ID 1.

    I'm joining 2 tables here:

    SELECT m.body, m.created_at
    FROM "conversation" c
    RIGHT JOIN message m ON c.id = m.conversation_id
    WHERE c.id = 1
    ORDER BY m.created_at DESC
    LIMIT 1;

    Entities:
    - user: who sent the message
    - conversation: chat room
    - user_conversation: many-to-many, to identify who created which chat room
    - message: information about chat message that was sent in whatever chat room
  • 0
    151d
    @Voxera what db should i use for a chat app? Im guessing nosql? Since the main db is postgres

    How about aws dynamodb?
  • 1
    151d
    ... or just load the profile data after the regular user.

    An ID based query is instant, if indexed properly.

    Many fall into the tarpit of avoiding additional queries writing complex queries which end up being slow or ticking timebombs, as the query plan is very fragile.

    Especially LEFT JOINs can become a real pain in the ass.
  • 1
    151d
    What's wrong with having just a user table, a user_roles table and a roles table to handle this logic?
  • 1
    151d
    @b2plane If you know the use patterns nosql are very fast but they are not as versatile as sql.

    Unless you plan to scale very large an sql will be fast enough.

    For learning though I do think its good to mix :). I have not used DynamoDB but it should work.
  • 3
    151d
    push all profile's fields into users table. then, write a CHECK that forces them to be null if user type is the one that can't have a profile, and not null otherwise
  • 0
    151d
    @MammaNeedHummus no need for separate roles table that would be an overkill. There will be 4 fixed roles. Having an entire table for roles means roles are prone to change
  • 0
    151d
    @IntrusionCM i already have 14 fields for User table. Merging Profile into User would make the User table have 19 fields. Isn't that a bit too much?
  • 0
    151d
    @kiki very good idea. Thats what I've imolemented in the first place minus the check part. But if i merge profile into user is it bad for the user to have 19 fields?
  • 0
    151d
    @b2plane the query seems as good as it gets.
    what i find strange is that many to many table user_conversation
    can multiple users create the same conversation? or does the table actually track who is added to a group convo.
    if its only about the creation the creator should be linked as foreign key "creator" in conversation.
    however if the table does represent the list of users of a conversation, this is a good solution.
  • 0
    151d
    @kleopi it tracks who participates in an already created group conversation. I mainly need a 1-to-1 chat but with this table i have scaled it so i can easily expand to group chats in the future if i need to. This way it will always behave like a group chat. 2 people privately chatting is kind of treated like a group chat
  • 0
    151d
    @b2plane depends, will it be thousands of rows or billions? :)
  • 2
    151d
    @Voxera hoping it remains in thousands but if it gets to millions it should generate millions of dollars already so i can hire more devs
  • 1
    151d
    @b2plane

    You can have separate tables and just load the profile data after user data.

    Thus N queries for each user with profiles.

    Thats what I meant by "avoiding multiple queries isn't always a good idea".

    As you already have an PK / index (e.g. userId in user_profile table) it will be not less performant....

    ... Most likely even be more ressourcefriendly as you avoid bloating up tables / result data with null values.
  • 0
    151d
    @IntrusionCM why not create an index on querying joined profile table with user table to improve performance. Am i missing something?

    But ok i have merged profile into user now. 19 total fields in 1 table. Sure this will be faster since I'll just make 1 api request and 1 db query and i got all user + profile data at once.

    But on a long enough timeframe how scalable and optimal this will be im not sure
  • 1
    151d
    @b2plane no, not at all. You won't notice any performance differences. The hard truth is, 95% of the projects will be scrapped or rewritten before the actual performance problems begin. If your project is in that lucky 5%, that means the business behind it is viable, and at the scale your solutions will break, you will have enough money and hiring power to fix it. That's my rule of thumb, and that's obviously only applicable to real code that's up to a reasonable standard, we're not talking bubble sort
  • 1
    150d
    @kiki it worked for facebook so yes :). Its definitely a viable strategy.
devRant © 2021 Hexical Labs LLC
Privacy Policy  |  Terms of Service
Add Comment