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?
