0

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

2 Answers 2

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)
4
  • Yes, a user can only be one role. How come it can't just be 0 or 1 or 2? Why is the extra table necessary? Would it be okay to just have the number without the extra table, then just handle what it means on the frontend/backend?
    – atkayla
    Oct 31, 2017 at 14:43
  • 1
    From a pure documentation standpoint, how do you know that 0 is Student, 1 is Teacher, 2 is Principal. It's just a number on a table. Secondly, what's to prevent me from updating one of those values to 9? What does 9 stand for. The Enum table provides a reference to equate the number to something meaningful AND prevents (using a foreign key) the inserting/updating the Role column to something that isn't valid. Oct 31, 2017 at 15:09
  • Naturally, you can use a check constraint to limit the values for the role, but the enum table still provides a reference to equate a 0 to a Student, a 1 to a Teacher, etc. Oct 31, 2017 at 15:13
  • Thanks! Makes perfect sense. This is for a school project, there are only students and teachers, and I'm the only one working on it, so 0 | 1 meaning nothing didn't even cross my mind. If this were a real project, the next person looking at the db would have no idea what they meant.
    – atkayla
    Oct 31, 2017 at 15:22
1

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)
3
  • Sorry, just the presentation layer. The way you show it is much better. Also, I did not know about the keywords, so thanks for that. Can you explain why the reference table is necessary? Why not just a set user_role = 1 | 2 | 3 and have my frontend/backend deal with what it actually means?
    – atkayla
    Oct 31, 2017 at 14:45
  • First rule: keep everything to do with data as close to the database as possible! If you have a "list", where is it going to be and where are you going to maintain it? What happens if a new role of 'Examiner' or 'PE Teacher' is created? Under my scenario, you just add the role to the table and carry on! Under yours, you have to find all instances of this list and update it! Lists a bit like CHECK CONSTRAINTs - if you are SURE* that you will only have a certain list (M/F), then use those, but if you add to it, you will have to double check *_all_ table definitions where they exist!
    – Vérace
    Oct 31, 2017 at 17:50
  • Databases are designed to cope with such a scenario! Lookup tables are their bread and butter, their raison d'être! If you don't have the constraint in a lookup table (or CHECK CONSTRAINT), then every time you write another app, the programmer will have to remember the set of valid values (think contractor!). If your data is worth anything at all, the chances are it will be reused. Keep data in the database and code in the code base - and avoid mixing the two!
    – Vérace
    Oct 31, 2017 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.