Primary Key Datatype for Single Column Table

user_1572283 0
Viewed 193

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:

  • Department Names in a company

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.

data-modeling datamodel mysql sql database
2 Answers

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.

Bill_Karwin_208600

Always use sequence or serial number for PK, the point is they are meaningless, since they are meaningless, there is no need to change it(after all what is the point to change one meaningless thing to another meaningless one?), then we can achieve stability.

user_13568740
Related