I am designing my first database, and I find myself frustrated by the choice between storing an integer or a string for each instance of a categorical variable.

My understanding is that if I have a table containing cities that I want to make a child of a table of countries, the most performant way to do that is to have the PK of the countries table as a FK in in the table of cities. However for ease of use and debugging, it's nice to always have the string name associated with the country PK. Every solution I have considered either is not recommended or seems overly complex.

I'd like opinions the merits of these approaches (or hear about new ones) and also to understand if it has to be this way or if databases simply are this way because of tradition.

Possible approaches:

  1. Use a string as a PK for countries. Then I will have a human-readable FK for it in any child tables. Obviously less performant than using integers, but I suspect it may be the least worst way to have the convenience I desire.

  2. Create a view using application logic that join each the string name of the country to the states table.

  • I don't love this because if the application logic breaks, the tables become less readable. Also I would expect large join operations to have an even worse performance penalty than string PK/FKs.
  1. Create a separate table to connect numeric IDs with the appropriate string ID. I'm not sure if it would be better to have a table coding each type of relation, or one big table with one big pool of IDs that cover all integer key-string value relations. I could then use application logic to look up the appropriate strings and fill the appropriate PK into the child table when it's string name is given by a user.
  • I feel like this might be pretty resource intensive too, as there would have to be a lookup every time a new row was added to the child. It also means that I would still have to create the views I want.
  1. Use enum data type. Instinctively, this would be my go-to approach, as it seems the ideal balance between natural and synthetic keys: Use integer IDs and give the IDs a string label so that the string itself need not be repeated.
  • Unfortunately my research has found that this is not recommended. One reason for that is that categories cannot be deleted easily. I'm not sure if that is dealbreaker for me, but I also wonder why DBMSs are designed this way. Aren't categorical variables commonly used enough to add convenience features for them?
  • 4
    Every option will have advantages and disadvantages but I wouldn't expect serious performance differences, not with (country) a 200 row table that is almost immutable. As a suggestion why not use the letter ISO code as a PK/FK (either the 2 or 3-letter code)? en.wikipedia.org/wiki/List_of_ISO_3166_country_codes Jul 24, 2022 at 19:32
  • If I did use a string at the PK, the ISO code would make most sense. However I just used countries as an example, I need a strategy that can go bigger than 200. For example if I have a neighborhoods table which is a child of the cities table which is a child of states and a grandchild of countries. How would I estimate the threshold of table size at which I would need a different approach?
    – Stonecraft
    Jul 24, 2022 at 20:01
  • 1
    "Create a view using application logic" - I don't see what the application has to do with this. Views are created at the database level.
    – Bergi
    Jul 25, 2022 at 9:56

1 Answer 1


While ypercube makes a good and logical point for the specific example of Countries, I would otherwise avoid using string-based data types because of the potential unexpected implications that can arise from certain assumptions different database systems make about strings. For example, in Microsoft SQL Server, the optimizer generally assumes VARCHAR columns are half full and will generate execution plans that request memory based on that assumption. This could result in over (or even under) allocation of memory resources to serve a single query. I imagine there are other interesting assumptions that other database systems also make around string-based data types, for better or for worse.

But even more important than performance is data accuracy. The number one job of a table is to store data, and ideally store it accurately. The number one job of a primary key is to establish uniqueness, and ideally it should be immutable. Surrogate keys have the benefit of ensuring all of these things remain true in the case when the human-readable value has the ability to change. This actually follows another good principal called one-field one-purpose because the surrogate key's meaning is completely decoupled from the business object's.

Going back to your Countries example, it's not usual that the name of a Country would change, but it's not impossible either. A few countries have changed names in the last 50 years. Even using the ISO code is not 100% a guarantee that it'll never change for a given Country because there is some meaning in how those codes are generated (albeit being more removed from the business object than using the human-readable value of the business object itself).

So if the natural key value is used, and is liable to change, the day it does change, now you risk data accuracy because not only do you need to ensure the Countries table is properly updated, you must do the same for every table that references Countries in a foreign key.

There's additional performance overhead with updating every record referencing the old value as well, of course, as opposed to just updating it in one place when a surrogate key is used as the primary key. But the bigger concern (going back to the primary goal of a table) is data accuracy, in my opinion.

Views are great tools for the job of unifying, transforming, and presenting the data to the application layer, and even help with data maintenance later on, in some cases, such as when your table structure needs to change. Since a view can act as a layer between the application and the database tables, there's less risk for the application when changing the structure of those tables. There's nothing inherently wrong with using them from a performance perspective, and JOIN performance (by the surrogate keys) should not be an issue with a properly architected and indexed database.

When is it better to do JOINs with a special lookup table vs have the human-readable columns in the parent table and have them inherit through the hierarchy?

It depends. For lack of a more articulate way to describe it, generally it makes sense to refactor the human-readable value into a separate table when it's currently being repeated in the main table it exists in. This is so there's a place to uniquely define that value that can easily and accurately be maintained. When done properly, this loosely follows the principals of normalization.

If by "special lookup table" you mean a single table (e.g. the enums table your post mentions) for multiple kinds of objects, I wouldn't recommend doing that. It may be easier to maintain than multiple separate object tables, but you lose some of the relational properties of a relational database system.

why surrogate keys can't have mutable string labels that propagate through all uses as an FK?

This goes back to data accuracy, primarily speaking. Nothing stops you from doing it, it's just not best practice because of the added risk against data accuracy, and makes data management harder and less performant when you need to update the value. You run the risk of lock escalation if it's a common value in the foreign keyed table, causing potentially longer wait times and blocking for read queries against that table.

why not deal with a changing country name by creating a new row for the new state + a column for years of existence?

Some people implement this design but more so because their business rules and use cases depend on historical data tracking. But for a regular transactional database with standard use cases, it inflates your data and still doesn't solve the aforementioned foreign key references where you'd have to update them with the changes too or inflate those tables as well. Even if I had the use cases to maintain historical data, I'd personally store the transactional history in a separate historical table from the active records.

  • Data consistency is a convincing argument for surrogate keys, but I still don't understand why surrogate keys can't have mutable string labels that propagate through all uses as an FK. Is this a design decision by DBMSs or would it be impractical for some reason? And why not deal with a changing country name by creating a new row for the new state + a column for years of existence?
    – Stonecraft
    Jul 24, 2022 at 21:53
  • 1
    @Stonecraft Please see my update answer which addresses each of your questions in your comment.
    – J.D.
    Jul 24, 2022 at 23:43
  • 1
    Thank you, that was very helpful.
    – Stonecraft
    Jul 25, 2022 at 0:10
  • @Stonecraft absolutely, best of luck!
    – J.D.
    Jul 25, 2022 at 0:22
  • 1
    @Stonecraft It sounds fine, but I don't personally use SQLAlchemy, though I've heard of it (and have used other ORMs). This is a feature particular to it that lives outside the database layer though, so it doesn't really have much to do with database design, and rather is meant to make development easier in the application I believe.
    – J.D.
    Jul 25, 2022 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.