I'm creating a new table that contains a 'user'. So far, the table looks like:

create table users(
  id primary key,
  code varchar

Currently, the "universe" of codes consists of: A or B.

Is it worthwhile, from a DB Design point of view, to create a codes table that contains all known codes?

create table codes(
  value varchar, -- with unique constraint
  description varchar

Then, I could update my users table's code column to have a foreign key relationship to the codes.table column.

Does this change seem to be useful for my above case?

  • 2
    Do you expect the number of codes to grow with time? Is there additional data to be stored, in addition to the code? Having a code table makes sense from a user administration point of view (i.e., if users of a front-end app need to be able to add codes to the list), or if there's additional data to store. If those aren't issues, it might suffice to have a CHECK CONSTRAINT on the column, so its value must be 'A' or 'B'. Even then, there's nothing inherently wrong with a separate table and foreign key - you just have other options. – RDFozz Jul 25 '17 at 17:31
  • Yes, add the code table and foreign key. – Michael Green Jul 26 '17 at 10:08

Browse other questions tagged or ask your own question.