Country specific fields in database schema What would be the best practice to design a database schema that can have specific country fields.

e.g. a sample customer table

id
name
email
social_security_number (USA person ID)
curp (Mexico person ID)
cpf (Brazil person ID)
zip (USA postal code)
cep (Brazil postal code)
postcode (UK postal code)


But as we have more specific fields for each country the table will end up with a lot of fields.

The other approach would be to use a code instead

id
field001 &gt; name
field002 &gt; email
field003 &gt; social security number for any country
field004 &gt; postal code for any country


I understand that each approach has it's pros and cons.
You could deal with such scenario by using Descriptive Metadata tables, have a look at the Wordpress database diagram.

As you can see it makes use of three metadata tables that can store any number of parameter per Post, Comment and User.

So in your case a bare-bones implementation could work like:

----------------
|     User     |
----------------
|uid| uname    |
----------------
| 1 | UserOne  |
| 2 | UserTwo  |
| 3 | UserThree|
----------------

----------------
|   MetaData   |
----------------
|mid |  value  |
----------------
|  1 |   TIN   |
|  2 |   SSN   |
|  3 |   UID   |
----------------

------------------------
|      UserMeta        |
------------------------
| id|uid|mid| value    |
------------------------
| 1 | 1 | 1 | 123131   |
| 2 | 1 | 2 | 343234-F |
| 3 | 2 | 1 | 123654   |
| 4 | 3 | 3 | OINASD-01|
| 5 | 3 | 1 | 123984   |
------------------------