mysql - Database for multiple types of users -

mysql - Database for multiple types of users -

i've been looking through different questions on here , can't find matches situation.

i designing database multiple types of users. have 1 main user table includes id, username, password, passwordsalt, accounttype (enum), , lastlogindate. need have multiple types of accounts: student, parent, schooladmin, systemadmin, coordinator, , teacher. thinking of having separate table each of these types of accounts, realized schooladmin, coordinator, systemadmin, , teacher share exact same data. these business relationship types have different permissions though. pupil , parent accounts have info have store.

i thought adding info 4 identical tables share user table , deleting tables, came across problem. need reference different types of accounts in other tables. example, had foreign key teacherid in club table show club sponsor is. if add together info user table , rid of other tables, how reference specific business relationship type in table?

i have never designed database help appreciated.

there 3 main ways of implementing inheritance on database models. please check links below, , study best 1 solve problem. nil improve start analyzing types of situations become architect.

single table inheritance

class table inheritance

concrete table inheritance

each of different approaches have pros , cons take wisely.

mysql database