CODEWITHSUNDEEP
mysqldatabase
dabecks
dabecks

Reputation: 153

Database for multiple types of users

I've been looking through different questions on here and I can't find something that exactly matches my situation.

I am designing a database for multiple types of users. I have one main User table which includes ID, Username, Password, PasswordSalt, AccountType (enum), and LastLoginDate. I need to have multiple types of accounts: Student, Parent, SchoolAdmin, SystemAdmin, Coordinator, and Teacher. I was originally thinking of having a separate table for each of these types of accounts, but I realized that SchoolAdmin, Coordinator, SystemAdmin, and Teacher all share the exact same data. These account types all have different permissions though. The Student and Parent accounts have extra information that they have to store.

I then thought about adding the information that the 4 identical tables share to the User table and then deleting those tables, but I came across another problem. I need to reference different types of accounts in other tables. For example, I had a foreign key for TeacherID in the Club table to show who the club sponsor is. If I add the information to the User table and get rid of those other tables, then how do I reference a specific account type in another table?

I have never designed a database like this so any help is appreciated.

Upvotes: 0

Views: 662

Answers (1)

jandresrodriguez
jandresrodriguez

Reputation: 794

There are three main ways of implementing inheritance on database models. Please check the links below, and study which is the best one to solve your problem. Nothing better to start analyzing this types of situations to become a good architect.

Single Table Inheritance

Class Table Inheritance

Concrete Table Inheritance

Each of the different approaches have their pros and cons so choose wisely.

Upvotes: 2

Related Questions