0

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.

4
  • 1
    NULL doesn't cost space sp tjat os irrelevant and self referencing is woth recursive cte easy to manage – nbk Jan 24 at 13:29
  • Based on the exclusive subtype solution, I don't think you understood the problem well, there is no way to identify if two rows in subtypes (PublicUser,AnonymousUser) belong to same row (User) in that pattern. As I stated "A user can have both public and anonymous profile" and their relationship should stay only as an internal detail. @bbaird – Batu G. Jan 25 at 16:44
  • @BatuG. Noted. You still have subtypes of the non-exclusive variety. There are well established patterns for dealing with this, namely a master entity with common attributes, then one table per subtype with their optional attributes. The link provided still provides context. – bbaird Jan 25 at 17:22
  • @bbaird Can you please elaborate with 2 simple table example. I seriously wanna solve this in the way you approach but can't figure out, how should I. – Batu G. Jan 25 at 20:45
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.
1
  • Great answer, thanks a lot helped me to understand a lot of abstract concepts 👍🏼 – Batu G. Jan 25 at 23:55
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.