Anonymous and Public User Table Design in twitter like application 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
User(id(PK),nickname,fullname,phone,is_phone_registered,public)

In this design I can have both anonymous and public profiles in same table. And records would look like:




id
nickname
fullname
phone
is_phone_registered
public




123
slayer
John Doe
+1 90 123 123 12 12
true
NULL


312
vendetta
NULL
NULL
false
123




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.
At first glance of your initial schema design, I liked the normalization of it, but I understand the problem you've run into. So my recommendation to solve that problem was to denormalize a little which essentially you did in your second solution. One table for all types of Users, self-referencing to link an Anonymous User to Public User. I don't know if it's necessarily considered an anti-pattern but it's definitely a common design solution in relational databases to use self-referencing tables.
My only recommendation on top of that is to add a dedicated field, such as a boolean, whose purpose is to define if the record is Anonymous vs Public (e.g. isPublic). That way, you can even have an Anonymous User who doesn't necessarily link to a Public User and that it's explicit it's an Anonymous User record (when the public field is NULL).