An address can belong to either the mailing or billing categories.
When a user adds an address to a table, should the address category be an independent integer value set by a constant somewhere in the source code, or should it be a foreign key to a table that lists the categories available?
I'm concerned about performance and best practice.
The chances of adding or removing categories are slim to none.
Best Answer
Performance is an excellent thing to consider but I find it helpful to worry about other costs like data quality. Referential integrity ensures that no values would be assigned that don't have a match in the list of values table. Everyone knows 1 is mailing, 2 is billing, piece of pie. FNG comes on board and makes a code change and oops, we now have addresses with an address type of 12. And we've had that problem for 2 months now, customers are angry that they have not received their product and they're filling complaints with the BBB. RI would have caused the app to blow up when they tried to push those bad values into the database.
"Nobody's going to make that mistake here." Fine, the app is a success and brings in lots of money. Eventually someone's going to want to know more about what's going on. We want reports and yes, we also said we need all these new features done by last week. So now everywhere where you've embedded that knowledge that 1 is billing and 2 is mailing you need to repeat in the reporting thing (Excel, traditional reporting systems, ad-hoc queries, etc) you give to the end users to make them happy. Now you've got that logic in two places so any changes to that logic has to be propagated across two systems.
Each time you carry logic like that across systems just adds to your technical debt. Maybe your system is never going to grow like that and it's fine to embed the logic in the app code but I've found the quickest way to achieve immortality is to implement a quick fix.
Oh and did you notice I switched up the values in the report example? The FNG who you brought in could make a rookie mistake like that and who knows how long it's going to take to catch the mistake.