When would you put data in multiple tables and when do you keep them in one and the same table? For example, what are the advantages and disadvantages of putting a system admin – who has exactly the same fields as another user type, except an additional Boolean for admin rights – in a separate table?
In my specific case: when I started to develop my app, I created two user tables because of the differences between these two user types. These differences were: 1) one user type had a relationship with a third table while the other user type did not; 2) the first user type had rights to make changes to this third model while the second user type did not; and 3) differences in the fields/variables for the two user types.
Reasons for integration: However, now my app has evolved in a direction where the fields between the two user types are basically the same. Moreover, it might be that a user the first type might need to be converted to the other user type, and vice versa. Converting the user type would be much easier if they were in the same table, with just a Boolean indicating the user type.
Reasons why not to integrate: On the other hand, the first two differences mentioned still apply, and I wonder if there aren’t security benefits in keeping the two user types in two separate tables. And not least important, I’m a newbie programmer and integrating the two tables into one table would be a highly difficult job for me given all the functionality developed around these two tables.
So in sum, how necessary is it to integrate the two tables to one table? Or more general, what would you argue is the rule of thumb when to put things in separate tables? There is a trade-off but how significant are these issues, particularly how much security benefit and speed benefit is there from having two seperate tables? I think I understand the themes of the trade-off but not their significant, thereby having difficulty deciding in which direction to go. Table inheritance would not the be the way to go in my situation: the choice would be to have two separate tables or just one table where I have a variable for user type.