How to segment account info and profile info

database-design

I am putting a rough SQL database design together and have gotten stuck on how to segment information about the users.

(Note before I get into anything, stored passwords are salted and hashed, I just write "password" for brevity)

For reasons I can't go into due to NDA, our database will have two user groups – human users and fake users. Human users will have login information (screen name, email address, password). Fake users only need a screen name. All users, human and fake, need profile information (first name, last name, gender, location, etc). Most of the profile information is only displayed on the user's profile page. However, depending on the user's settings, either their real name or screen name may be displayed under their avatar throughout the front-end. Email address is not visible in the user's public profile.

I can't decide how to segment this information into tables. My first instinct was to have three separate tables,

  • "accounts" (for humans – screen name, email, and password)

  • "user_profiles" (first name, last name, gender, location, etc)

  • "fake_user_profiles" (screen name, first name, last name, gender,
    location, etc).

But this runs into several problems:

  • Each user, human or fake, needs a unique ID – no human should have
    the same ID as any fake user. I would have to enforce UIDs across two separate tables
  • I have two almost identical tables with
    only one field different between them

My next idea was to put all of the users, human or fake, together. Then I'd have two tables:

  • "accounts" (screen name, email, password)
  • "profiles" (first name, last name, gender, location, is_human, etc)

With this approach, it would make sense to put the UID for each user (human or fake) into the "accounts" table. But what would I do with the "email" and "password" fields that should be non-null for humans but serve no purpose for fake users?

Would there be a better approach than either of the two I've described? Would it be better to put all the information in one table?

Best Answer

Unless there is a security problem (you want someone to view some information, but not the other), there is no need to; however in many sql platforms you can deny access to specific fields in security or just create a view. The only other reason you would want to have two tables is if you have a business reason to segregate the data to two separate tables. Otherwise, if you are constantly needing to get information from table A and table B at the same time, you have to join each time which will add to overhead.

Related Question