0

I am currently working on a project where we have 2 types of users.

I have a general Users table for their User_ID, user_name, etc all the data that is similar for both users.

I looked online and found that its common to make 2 separate tables and just have the differences in these tables.

Since now I have two tables (A and B), my primary key cannot be the same as my User_ID as I have 2 databases. If User 1 created account A it would match, but if User 2 created account B, the User_ID is 2 while the primary key in the table would be 1.

Is it best to manually set the primary key to be the same as the user_ID? Or what is the best way to handle this?

DjangoBlockchain
  • 534
  • 2
  • 17
  • 1
    You have a "general Users" table . . . and what else? What is `A`? What is `B`? What is "account A"? "Account B"? Sample data would really help convey what you are trying to do. – Gordon Linoff Aug 04 '17 at 03:33
  • Table B should have a foreign key to Table A, which should be the master user table. – user207421 Aug 04 '17 at 03:34
  • Why not have one table for all users and distinguish them by the value of a field added for the purpose, say, for example, an enum of user type. – RichGoldMD Aug 04 '17 at 03:41
  • Sorry I should have added some more detail! So the project has two types of users, and it just UserA has to put down their telephone # and address, while UserB just has to input their email address. So instead of having 1 table with Null values, I decided to split up the tables into UserA (contains primary key, tele# and address) and UserB(contains primary key, email address). I am curious as what to use for my primary keys for UserA and UserB. If I start at 1 and auto increment, I cannot use User_ID as a foreign key for UserA and UserB, as there is potential overlap. – DjangoBlockchain Aug 04 '17 at 03:43
  • I think you should just have a single table with nullable columns, or else normalize fully and have *three* tables: a UserID table, and a phone-no+address table and an email table, both with UserID foreign keys, which is clearly overkill. You should certainly not have two user tables of equal standing where a new UserID can be inserted into either. That's not normalized. – user207421 Aug 04 '17 at 03:44
  • If I were to take your 2nd suggestion and normalize fully and have three tables, what would you use as a primary key for the phone-no+address and the email table? As in, from the first UserID table, how are you able to distinguish which User you are trying to access? – DjangoBlockchain Aug 04 '17 at 03:47
  • It doesn't really matter what you use. You could use UserID again, or you could have an auto-incrementing integer ID column. From the UserID table you just do joins on UserID with the other tables. Again this seems like major overkill to me. – user207421 Aug 04 '17 at 03:48
  • Okay so as an example, say the project creates 4 UserA and then 1 UserB. Wouldn't the UserID's be "1,2,3,4,5", but the primary key values be "1,2,3,4,1", as you are adding the first entry to the UserB table? From there, with a User_ID of 5 and the primary key of UserB being 1, how do I connect the two? – DjangoBlockchain Aug 04 '17 at 03:50
  • Via a join on UserID. I said that. If you use an autoincrementing primary key for the secondary tables it doesn't matter what the hell its values are. But don't do this. All it amounts to is an expensive way to implement nullable columns. There is no benefit. – user207421 Aug 04 '17 at 03:51
  • Sorry I am a bit new everything, when you mention that "I could user UserID" again, I could just put the UserID as the primary key in the table? – DjangoBlockchain Aug 04 '17 at 03:54
  • Exactly so, making it both a primary key and a foreign key, if that is possible. – user207421 Aug 04 '17 at 03:55
  • Duplicate of [MySQL Question - How to handle multiple types of users - one table or multiple?](https://stackoverflow.com/a/1054099/207421). – user207421 Aug 04 '17 at 03:56
  • Thanks will check it out! – DjangoBlockchain Aug 04 '17 at 03:58

1 Answers1

1

I looked online and found that its common to make 2 separate tables and just have the differences in these tables.

I'd be surprised. That's not a normalized schema.

I think you should just have a single table with nullable columns, or else as per this answer here, or else (madly) go the whole hog: normalize fully and have three tables: a UserID table, and a phone-no+address table and an email table, both with UserID foreign keys, which is clearly major overkill. If you use secondary tables you just do joins on UserID from the UserID table.

You should certainly not have two user tables of equal standing where a new UserID can be inserted into either. That's not normalized.

user207421
  • 305,947
  • 44
  • 307
  • 483