How to design database using varying number of columns in table

Question

Let us say that you are creating a system to store characteristics of different countries. There will be same basic columns like name, population, capital city etc. But let us say in addition to it you want to store some country specific information like highest mountain, nearest ocean, most famous food etc. These columns will be different for each country.

How can this be done using a relational database like MySQL. I know this is easier using a schema-less NoSQL database like MongoDB where each country can be stored as a separate document. But can something like this be done using relational DBs?

Answer 1

With only text fields you need two additional tables:

• properties (contains the name of a property, eg "highest mountain")
• country_properties (contains values for country-property pairs: eg: id of country "austria", id of property "highest mountain", "name of the mountain")

Alternatively, if there are only a couple of properties, simply store NULL for unknown values.

Answer 2

It can. As I learned today by asking another question on SO, this is called EAV (for Entity-Attribute-Value model). I found an interesting explanation about it on wikipedia.