Search code examples
codeigniterdatabase-designdatabase-schemauser-management

Database design issue : Multiple types of users

I am designing a database that has multiple types of users, i.e. Administrators, Editors, Teachers and Students. Now all these users have some common fields (CF) and some unique fields (UF) associated to them. I was wondering if this is a good design?

Table Users: [user_id (PK), CF1, CF2,..., CFN, user_type (enum)]

Table Admin: [id (PK), UF_A1, UF_A2, ... , U_AN, user_id(FK)]

Table Editors: [id (PK), UF_E1, UF_E2, ... , U_EN, user_id(FK)]

Table Teachers: [id (PK), UF_T1, UF_T2, ... , U_TN, user_id(FK)]

Table Students: [id (PK), UF_S1, UF_S2, ... , U_SN, user_id(FK)]

where UF_A, UF_E, UF_T and UF_S are unique fields for each of the respective tables.

Now my questions are:

  1. Is this a good design? If not, how will you design it?
  2. How do I ensure that a user of user_type teacher is not stores in students table for example?

PS: Some more points if they might help in getting a better insight:

  1. The database will be used with codeigniter.
  2. Examples of CF are: username, password, email, profile picture
  3. Examples of unique fields are: Students(age, enrollment number), Teacher (univ. name, univ. logo, academic degree)
Solution

  • Your design (Users.user_type) implies that a particular person can be an Admin, or that person can be an Editor, but can't be both. It also implies that teachers can't be editors. Is that what you intended?

    The columns Admin.user_id, Editors.user_id, Teachers.user_id, Students.user_id are usually primary keys in the kind of structure you're describing. If CodeIgniter won't let you make them primary keys, you still need to declare them unique.

    Examples of unique fields are . . . Teacher (univ. name, univ. logo, academic degree)

    That's probably not true.

    How do I ensure that a user of user_type teacher is not stores in students table for example?

    With overlapping constraints, default values, check constraints, and foreign key references. It's easier than it sounds. See this SO answer. But I'm not convinced you really want to do this.