How should this one-to-one link be modelled?

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.)

Hi @neon124,

I might expect this to be posted on another forum which is not Processing related but rather SQL / database modelling focused :wink:

Hi @neon124,

agree with that:

but also don’t understand you problems…

You have users for the system, which needs a loginname and a password (other attributes like real names etc doesn’t matter on this stage).
loginname can be whatever it is (AP name, email, etc),
password with some security standards

With this information you look if the user credentials are correct on login.

The other things you are talking about are roles. So the users in you system need a role applied (ie Admin, regular user, or other special roles)

As the user successfully logged in you can decide on the role what he/she it able to do on the application !?


Table User
unique id, username, encpassword,Role [ref roleid], …
1,user1,xxxxxxxx,1, firstname, lastname, etc
2,user2@domain.xxxxxxxx,2, firstname, lastname, etc

Table Role
unique roleid, rolename, permissions
2, Manager, [READ,WRITE]
3, Regular, [READ]

No magic… :man_shrugging:

— mnse