Anonymous and Public User Table Design in twitter like application

Question

I have schema design problem which I am not 100% sure if my assumptions are correct.

So the requirements are:

1. I need to have 2 different type of user, anonymous and public.
2. A user can have both anonymous and public profile.
3. A public and anonymous user can follow anonymous and public.
4. 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:

1. Single responsibility in tables
2. Get rid of a lot of redundant null's
3. Feels like self referencing column is an anti-pattern, which is public field is referencing to other rows id.

All suggestions are welcomed.

Answer 1

Based on your requirements, a user can have a public and anonymous account - however, those must be kept separate, which necessitates a exclusive subtype so different accounts have different UserIds/NickNames. But we also need to maintain a relation between the public and anonymous accounts. Since a Public user can't be owned by an anonymous account, the relationship is reserved for the anonymous user subtype.

CREATE TABLE UserType
(
  UserTypeCd  CHAR(1)      NOT NULL  /* P - Public, A - Anonymous, etc */
 ,Name        VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_UserType PRIMARY KEY (UserTypeCd)
 ,CONSTRAINT AK_UserType UNIQUE (Name)
)
;

CREATE TABLE User
(
  UserId      INT          NOT NULL
 ,UserTypeCd  CHAR(1)      NOT NULL
 ,NickName    VARCHAR(20)  NOT NULL
 ,CONSTRAINT FK_User_One_Of_UserType FOREIGN KEY (UserTypeCd) REFERENCES UserType (UserTypeCd)
 ,CONSTRAINT PK_User PRIMARY KEY (UserId)
 ,CONSTRAINT AK_User UNIQUE (NickName)
)
;

CREATE TABLE PublicUser
(
  PublicUserId  INT           NOT NULL
 ,FullName      VARCHAR(100)  NOT NULL
 ,PhoneNumber   VARCHAR(15)   NULL
 ,CONSTRAINT FK_PublicUser_Is_User FOREIGN KEY (PublicUserId) REFERENCES User (UserId)
 ,CONSTRAINT PK_PublicUser PRIMARY KEY (PublicUserId)
)
;

CREATE TABLE AnonymousUser
(
  AnonymousUserId  INT  NOT NULL
 ,PublicUserId     INT  NOT NULL
 ,CONSTRAINT FK_AnonymousUser_Is_User FOREIGN KEY (AnonymousUserId) REFERENCES User (UserId)
 ,CONSTRAINT FK_AnonymousUser_Owned_By_PublicUser FOREIGN KEY (PublicUserId) REFERENCES PublicUser (PublicUserId)
 ,CONSTRAINT PK_AnonymousUser PRIMARY KEY (AnonymousUserId)
 ,CONSTRAINT AK_AnonymousUser UNIQUE (PublicUserId) /* Use if a public user can only have one anonymous account */
)
;

The last requirement is to create a check to ensure the exclusivity between PublicUser and AnonymousUser is maintained - this can be done through a function and check constraint (SQL Server, Sybase) or an AFTER INSERT trigger (everything else). Basically, after the row is inserted into one of the subtype tables, the trigger will query User to get the UserTypeCd. If the code matches, the transaction is allowed to commit. If not, the transaction is rolled back.

I'm working under the assumption an anonymous user must be associated with a public user. If this is not the case, we implement PublicUserId as an optional column like so:

CREATE TABLE AnonymousUser
(
  AnonymousUserId  INT  NOT NULL
 ,CONSTRAINT FK_AnonymousUser_Is_User FOREIGN KEY (AnonymousUserId) REFERENCES User (UserId)
 ,CONSTRAINT PK_AnonymousUser PRIMARY KEY (AnonymousUserId)
)
;

CREATE TABLE AnonymousUserOwner
(
  AnonymousUserId  INT  NOT NULL
 ,PublicUserId     INT  NOT NULL
 ,CONSTRAINT FK_AnonymousUserOwner_For_AnonymousUser FOREIGN KEY (AnonymousUserId) REFERENCES AnonymousUser (AnonymousUserId)
 ,CONSTRAINT FK_AnonymousUserOwner_Is_PublicUser FOREIGN KEY (PublicUserId) REFERENCES PublicUser (PublicUserId)
 ,CONSTRAINT PK_AnonymousUserOwner PRIMARY KEY (AnonymousUserId)
 ,CONSTRAINT AK_AnonymousUserOwner UNIQUE (PublicUserId) /* Use if a public user can only have one anonymous account */


)
;

Other notes:

1. It looks like Is_Phone_Registered is just an indicator if PhoneNumber is NULL. If this is the case, there is no need to store this value, it can be derived.
2. If you are using Id columns, you need to name them within the context of each entity, so UserId, TweetId, etc.
3. For Relationship, there is no need for the Id column, your primary key is (FollowingId,FollowerId) (both FK references back to User). No additional columns are needed.

Answer 2

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).