I have schema design problem which I am not 100% sure if my assumptions are correct.
So the requirements are:
- I need to have 2 different type of user, anonymous and public.
- A user can have both anonymous and public profile.
- A public and anonymous user can follow anonymous and public.
- Anonymous user and public user must not be identifiable by clients. Which means if I know public user I shouldn’t be able to fetch anonymous profile details vice versa.
I first come up with idea:
User (id, anon_id,public_id) AnonymousUser(id(PK,FK),nickname) PublicUser(id(PK,FK),nickname,fullname,phone,is_phone_registered) RelationShip(id,follower_id(FK-User),following_id(FK-User)) Tweet(id, author_id(FK-User),text)
In this design I could split users into two. However, if we know the tweet’s author id we are not able to understand if it’s send by public or anonymous account. And also the same problem exist in Relationship too, we are not able to understand if follower/following is anonymous or public. The solution might be adding 2 more fields to Relation clarify follower/following type ie. follower_type/following_type and 1 more field to Tweet author_type.
Another solution which I felt more correct is
In this design I can have both anonymous and public profiles in same table. And records would look like:
|123||slayer||John Doe||+1 90 123 123 12 12||true||NULL|
And public field should not be visible to clients so I am pretty much achieving my goals on security side of things.
Sorry for my ignorance I am having NoSql background and in sql world I feel like I need achieve:
- Single responsibility in tables
- Get rid of a lot of redundant null’s
- Feels like self referencing column is an anti-pattern, which is public field is referencing to other rows id.
All suggestions are welcomed.