0

I have a theory question. Let's say I have a table named "Worker" for people working in an office, and one of the columns would be "Role". I can add a constraint (CHECK IN ("director", "HR", "Secretary", "PA", etc...). What would be the benefits of creating a separate new table ("Roles") with all possible roles and and an id to relate both tables?

Cvg
  • 53
  • 1
  • 5
  • https://en.wikipedia.org/wiki/Database_normalization#Normal_forms – smnbbrv Dec 15 '16 at 16:02
  • Easier to maintain, for one thing. When a role is added or removed (or its name is changed), you won't have to change check constraints - which you may also forget to do. Easier to write queries where you need results by role, too. –  Dec 15 '16 at 16:03

2 Answers2

1

What would be the benefits of creating a separate new table ("Roles") with all possible roles and and an id to relate both tables?

Here are some off the top of my head:

  • Any additional roles would be a change to the table definition rather than a standard INSERT to a table
  • You can use the data in the table to drive UI items like list boxes
  • If you rename/add/delete a role you'd have to drop the constraint, update all records in the Worker table, and re-add the new constraint
  • You can add ancillary data about a role without adding columns to Worker
  • You would have to replicate the constraint in layers outside of the DB
D Stanley
  • 149,601
  • 11
  • 178
  • 240
1

The check constraint would be written as:

(CHECK IN role ('director', 'HR', 'Secretary', 'PA', ...)

The advantages of storing the roles in a separate table. Here are some:

  • You have a list of all roles that can be accessed by anyone else.
  • You can provide additional data about the role -- translation to another language, abbreviation, salary grade, and so on.
  • Adding or modifying an existing role only requires changes to data, not to the structure of the database.
  • You can provide metadata, such as date created and who created the role.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786