0

I have 4 tables

  • Account
  • Admin
  • User
  • Role

The account table contains the email and password to login.
An account can either be an admin or an user.
An user has a registerdate
An admin has an adminnumber, but no registerdate (different attributes)
The role table contains the role name, either admin or user.

An account currently contains foreign-key relationships with all other tables. This isn't ideal, since depending on the role, one of the tables will be left empty.

I need all account to be in the same table, yet depending on the role it should have different links and attributes.

What is the best way to tackle this?

I use MSSQL

Charlieface
  • 52,284
  • 6
  • 19
  • 43
KlaasOrnak
  • 11
  • 3
  • Does this answer your question? [What is the best way to implement Polymorphic Association in SQL Server?](https://stackoverflow.com/questions/7000283/what-is-the-best-way-to-implement-polymorphic-association-in-sql-server) – Charlieface Oct 10 '21 at 16:05
  • Recommended method is to add a `UserOrAdmin` table, to which `Account` has a FK to, and then `User` and `Admin` both have their primary keys foreign-keyed to that table (in other words they have a PK which is also a FK). You may also want a `Type` column – Charlieface Oct 10 '21 at 16:07
  • _and password to login_ I hope not. That is poor security. It should contain a hashed value. See [how to store passwords](https://www.geeksforgeeks.org/store-password-database/) – SMor Oct 11 '21 at 02:09

0 Answers0