So i am working a project which have up to 3 different user types, there are about 30 fields which are exactly the same between all 3 users, then an additional 20 fields belonging to only 2 of the 3 user types(admin and player). And finally there are another 11 fields specific to only the one user type.(player)
All 3 user type have different uses in the system. One is a admin, one a user and the another a palyer in the system.
Each user’s role in the application is different. And each user will hook up to different tables dependent on their type.
My question is thus, should I duplicate the table structures into separate tables for each user and then extend each table separately or should I keep a general user table and extend all user type from that table with nullable foreign keys, determining the joining table by the user type.
the answer is somewhere between those two extremes
do a search for supertype/subtype
you’ll want a main users table, which contains all common columns, and then separate subtype tables for each different type of user, containing only those columns unique to each type
the subtytpe tables will use the supertype’s PK as their own PK but have it as FK as well
Please correct me if I am wrong, you are suggesting I create a user table to keep all fields relevant to all 3 user types and then create a table for each user type. The tables per user type, subtypes, will then reference the user table, supertype, with a one-to-one relation?