1

I am building an e learning website. I have three types of users : novice, intermediate and expert. I have 3 tables: lecture,chapter and pages.For each of these table I have to differentiate them by adding for which type of user is that lecture,chapter and page. Is this a good practice to add same column name category (which can be either novice, intermediate or expert) to all the 3 tables ?

Neha
  • 149
  • 2
  • 16
  • It's a find practice. You will need to alias the tables when you select them anyway. So, something like "name" would be "table1.name", "table2.name", etc... – durbnpoisn Jan 11 '16 at 14:02
  • so i won't be penalize for this practice. Because if i create a new table named `category` i will have only 2 fields in that just with the `ID` and `type` -(novice/inter/expert) – Neha Jan 11 '16 at 14:05

1 Answers1

1

If you want to add more characteristics later to your user categories, the best is to create a separate table for this (eg: user_categories) and refer to this table in all 3 content tables. It would look like this:

CREATE TABLE user_types (id int PRIMARY KEY, name VARCHAR(20));
CREATE TABLE lectures (id int PRIMARY KEY, user_type int REFERENCES user_types(id));

You can also refer to this post to decide how to proceed in your case:How far to take normalization in database design?

Community
  • 1
  • 1
Clémentine
  • 468
  • 1
  • 5
  • 16