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
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.
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)
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
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');
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;
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 🙂