So Let me display a user's country or perhaps a country dropdown menu.
Let us say I've got a customers table :
CREATE TABLE users ( "id" serial, --PK "name" character varying, ... "country" integer --FK );
The nation is really a foreign key that rerefences the PK "id" column of "products_country" table
CREATE TABLE items_countries ( "id" integer, --PK "text_en" character varying, "continent" integer --FK to table "items_continent" );
I usually designed my database by doing this: a number of my programs needed near 100 type of "products".
For products having a large group of values, I'd create its very own database table ("products_nations") and grouping others in a single huge "products" table.
This method pleased my clients who needed, using a database web-based administration, to alter the label in order to give a value within the products specific for their work. But take into account that many of these products never change (nations, gender...), could it be worth to keep them within the database ?
How about an static array approach ? Pros: reduce the amount of database tables ?, Cons: no foreign key, no remove cascade/verification.
It isn't about performance issues, because both method can lead to caching, but instead a database/application design question.
What's your method for this ?
In my opinion, this really is dependent around the application and user needs. I've done both previously.
Generally, if there's a relatively small group of data that rarely changes, coding it inside a library or perhaps a configuration file may be the direction I'd take. Once the data will get too big or must be associated with more information, i quickly may likely place it within the database.
One factor I additionally do when determining which path to take would be to list the professionals and also the cons of putting the data in code / inside a database after which come to a decision based on the data that emerges.