# 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

--Fails due to Role type 4 not in Role enum table
``````

### 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 KEYWORD`s (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 🙂