I have a number of entities that represent the various user kinds that must be able to access a certain system. They also come with a variety of information attached to them.
For instance, “admin user” has a workstation number and “ordinary user” has an email address (note that this is a hypothetical case). Additionally, both parties share similar attributes including first and last names, addresses, and phone numbers. Last but not least, they obviously need a (unique) username and password to get in.
The user only has to enter his user name and password in the program, and depending on the user type, the functionality of the application varies somewhat. You can assume that the username for this task has to be distinctive.
How can I effectively simulate this?
I can’t just make two tables since then I can’t make sure the user name is unique.
Additionally, I am unable to include them all in a single table due to the many types of unique data that are related to each.
I believe I might need three different tables: one for “users” (with user name and password), one for “regular users,” and another for “admin users.” But how would the relationships between these three databases function? Or is there another approach?
( By the way, since MySQL is the target DBMS, I doubt the database system itself supports generalization.)