I am working on a database model and have some tables(let's call them T-table) which will always have a very limited number(< 100) of records. And these T-tables will have only one column of varchar(100). And these T-tables won't change frequently, probably once in a blue moon. Example of such Tables:
Now, I am confused between: Having one single column(e.g. Departments(varchar and primary key)) type varchar table and the same table with two columns ID(int and primary key) and department(varchar).
Could anyone please explain which one makes more sense in my scenario?
Many thanks in advance.
Most developers call these tables lookup tables.
Think about whether the department names are allowed to change. I'm not talking about how likely they are to change, but if it could happen at all. In fact it's even more important to consider this if it happens rarely.
If you use the single column table with the varchar as the primary key, and a department name changes, how will you update the rows in other tables that reference it? You only need to update one row in the Departments table, but you might have to update hundreds of rows that reference that department in an Employees table or Budgets table, or thousands of rows in a Reports table, or millions of rows in a Sales table.
If you use the integer primary key with associated varchar for the Department name, then there's no need to change all those other rows that reference the department. They can continue referencing it by its integer, even if the name changes.