I have to design a database for an office. They have division and section.
I thought of making 2 tables, division and section.
table division
division_id
division_name
table section
section_id
section_name
division_id
section_id is primary key and auto increment in section_table.
division_id is primary key and auto increment in division_table.
I want to know which is better here, should i have to remove division_id from division table and section_id from section table and put division_name instead of division_id, since division_name and section_name are unique?
In most cases I prefer to play it safe and use a surrogate key, rather than synthetic. I generally only use synthetic keys with tables that essentially hold “enum” values or values that are not manipulated nor referenced individually by a user. I would probably prefer to leave the auto increments in tact here. Data is always easier to reference, especially via URL using an integer. Will section and division have similar data or not really?
a lot depends on the size of the actual names in use
there’s no problem avoiding surrogate keys if your division names are like ‘Accounting’ and your section names are like ‘Bean Counting’
however, if your division names are like ‘Corporate Finance and Investments’ and the section names are equally long and unwieldy, then you might want to use ids
another thing to consider is how often (i.e. in how many tables) there will be foreign key references to divisons and sections