[Database] How to model this one-to-one relation?

Question

I have several entities which respresent different types of users who need to be able to log in to a particular system. Additionally, they have different types of information associated with them.

For example: a "general user", which has an e-mail address and "admin user", which has a workstation number (note that this a hypothetical case). Both entities also share common properties like first name, surname, address and telephone number. Finally, they naturally need to have a (unique) user name and a password to log in.

In the application, the user just has to fill in his user name and password, and the functionality of the application changes slightly according to the type of the user. You can imagine that the username needs to be unique for this work.

How should I model this effectively?

I can't just create two tables, because then I can't force a unique constaint on the user name.

I also can't put them all in just one table, because they have different types of specific information associated to them.

I think I might need 3 seperate tables, one for "users" (with user name and password), one for the "general users" and another one for the "admin users", but how would the relations between these work? Or is there another solution?

(By the way, the target DBMS is MySQL, so I don't think generalization is supported in the database system itself).

Answer 1

Two tables. USERS with user names, first, last, etc. ROLES with roles, and a link back to the user name (or user id or whatever). Put a unique constraint on the user name. Put workstation nbr, email, phone, whatever else you need, in the user table. Put 2 columns in the ROLES table -- USERID and ROLE.

Answer 2

Your 3 tables approach seems Ok. In users table have only ID, username, password,usertype. In general users table have ID, UserID (from users table), other fields. Same thing for admin users.

Usertype field will tell you from what table to search for additional info

if(usertype==admin)
  select * from  admins where userid=:id;
else    
  select * from  general where userid=:id;

Answer 3

You should decide how much specific information is being stored (or likely to be stored in the future) and make the decision based on that. If there are only a handful of fields for each user type then using a single table is alright.

USERS table (name, type, email, password, genfield1, genfield2, adminfield1, adminfield2)

Make sure to include the type (don't assume because some of the fields particular to that user are filled in that the user is of that type) field. Any queries will just need to include the "AND usertype = " clause.

If there are many fields or rules associated with each type then your idea of three tables is the best.

USERS table (ID, type, name, password)
GENUSERS (ID, genfield1, genfield2)
ADMINUSERS(ID, adminfield1, adminfield2)

The constraints between IDs on the table are all you need (and the main USERS table keeps the IDs unique). Works very well in most situations but reports that include both types of users with their specific fields have to be done in two parts (unioned SQL or subqueries or multiple left joins).

Answer 4

You can solve it with one 'general' users table containing the information thats available for all users and 1 table for every specific user type. In your example you will then need 3 tables.

Users: This table holds only information shared between all usertypes, ie. UserId, Name, Address, etc.

GeneralUsers: This table 'extends' the Users table by providing a foreing key UserId that references the Users table. In addition, information specific to general users are held here, fx. EmailAddress, etc.

AdminUsers: As with GeneralUsers, this table also 'extends' the Users table by providing a foreign key UserId referencing the Users table. In addition information specific to admin users are held here, fx. WorkstationId, etc.

With this approach you can add additional 'specializations' if the need arises by simply adding new tables that 'extends' the Users table using a foreign key reference. You can also create several levels of specialization. If for example admin users are general users as well as admin users then AdminUsers could 'extend' GeneralUsers instead of Users simply by using a foreing key to GeneralUsers instead of Users.

When you need to retreive data from this model you need to which type of user to query. If for example you need to query a GeneralUser you will need something similar to:

SELECT * FROM GeneralUsers LEFT JOIN Users ON GeneralUsers.UserId = Users.UserId

Or if querying an admin user

SELECT * FROM AdminUsers LEFT JOIN Users ON AdminUsers.UserId = Users.UserId

If you have additional levels of specialization, for example by having admin users also being general users you just join your way back.

SELECT * FROM AdminUsers LEFT JOIN GeneralUsers ON AdminUsers.UserId = GeneralUsers.UserId LEFT JOIN Users ON GeneralUsers.UsersId = Users.UserId

Answer 5

I most definitely would not do a model where you have separate tables as in GeneralUser, AdminUser and ReadOnlyUser.

In database design, a good rule of thumb is "Down beats across". Instead of multiple tables (one for each type), I would create a SystemUsers table, and a Roles table and define a join table to put SystemUsers in Roles. Also, I would define individual roles.

This way, a user can be added to and removed from multiple roles.

A role can have multiple permissions, which can be modified at any time.

Joins to other places do not need a GeneralUserId, AdminUserId and ReadOnlyUserId column - just a SystemUserId column.

This is very similar to the ASP.Net role based security model.