Storing whether a user is a student or teacher? Is a lookup table necessary?

All we need is an easy explanation of the problem, so here it is.

Users can be either student or teachers, and are able to do different things depending on which role they are. Can I just use role with a 0 or 1? I usually see lookup tables which map a user to a role to permissions and whatnot, but would that be overkill for this scenario?

user
---
id      1
name    John
role    0

id      2
name    Jane
role    1

or

user
---
id      1
name    John
role    1

id      2
name    Jane
role    2

role
---
id      1
type    Student

id      2
type    Teacher

id      3
type    Principal

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Your sample data suggests a User can be a Principal, so 0 and 1 won’t work. IF a User can only be ‘a student’ OR ‘a teacher’ OR ‘a Principal’ OR (a Janitor, a Librarian, etc.), this looks like a simple enumeration pattern and your second example would be the best approach. Make sure your User table has a foreign key defined that points to the Role table to ensure that only valid Roles are used

Create Table dbo.Role (ID INT, RoleName varchar(30)
CONSTRAINT [PK_Role] PRIMARY KEY ([Id]))
Insert into dbo.Role (ID,RoleName) 
values(1,'Student'),(2,'Teacher'),(3,'Principal')

Create Table [User] (ID INT, UserName varchar(30), [Role] int
CONSTRAINT [PK_User] PRIMARY KEY ([Id])
CONSTRAINT [FK_User_Role] FOREIGN KEY ([Role]) REFERENCES [Role] ([Id])
)

--Works
Insert into dbo.[User] (ID,UserName,Role) values(1,'John',1),(2,'Teacher',2)

--Fails due to Role type 4 not in Role enum table
Insert into dbo.[User] (ID,UserName,Role) values(3,'Sam',4)

Method 2

I’m not sure why your data is in vertical rather than horizontal format,

user
---
id      1
name    John
role    0

i.e. if that’s your presentation layer or whether you’ve used the EAV anti-pattern.

Also, you should not use table names using SQL KEYWORDs (such as user and role). You can normally “escape” table names (i.e. using double quotes), but it’s always better to use an alternative – it also makes any system more portable.

To answer your question – you need a lookup (AKA a reference) table.

You want to do something like:

CREATE TABLE my_role
(
  role_id INTEGER NOT NULL,
  role_desc VARCHAR (30) NOT NULL,
  CONSTRAINT role_pk PRIMARY KEY (role_id)
);

INSERT INTO my_role 
VALUES
(1, 'Student'),
(2, 'Teacher'),
(3, 'Princpal');

and:

CREATE TABLE my_user
(
  user_id INTEGER NOT NULL,
  user_name VARCHAR (30),
  user_role INTEGER NOT NULL,
  CONSTRAINT user_pk FOREIGN KEY (user_id) REFERENCES my_role (role_id)
);

INSERT INTO my_user
VALUES
(1, 'John', 1),
(2, 'Jane', 2),
(3, 'Fred', 3);

Then, the query you can use to get back your data would be something like:

SELECT 
  u.user_id, u.user_name, u.user_role, 
  r.role_id, r.role_desc
FROM
  my_user u
JOIN my_role r
ON   u.user_role = r.role_id;

Results:

 user_id | user_name | user_role | role_id | role_desc 
---------+-----------+-----------+---------+-----------
       1 | John      |         1 |       1 | Student
       2 | Jane      |         2 |       2 | Teacher
       3 | Fred      |         3 |       3 | Princpal
(3 rows)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply