Mysql – Anonymous and Public User Table Design in twitter like application

database-designerdMySQLpostgresqlschema

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.

Best Answer

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.
Related Question