0

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?

  • Your second approach is best. Just leave any field that is not necessary as null. If you have the field set as a varchar it'll hardly use any space so not a problem. But why have two tables? You are not going to have more than one profile per account (or the other way around) are you? – Chris Woods Apr 15 '15 at 20:30
  • Thanks for your input! It seems to me like bad form to bundle login information like email and password in the same table as public profile information, but maybe I'm wrong about that – user45623 Apr 15 '15 at 20:42
  • 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. – Chris Woods Apr 17 '15 at 13:15
1

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.

  • Thanks Chris! I can't upvote you because my reputation is low, but can still give you "right answer"! – user45623 Apr 20 '15 at 22:01

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.