5

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 > name
field002 > email
field003 > social security number for any country
field004 > postal code for any country

I understand that each approach has it's pros and cons.

1

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   |
------------------------

enter image description here

1

If you have a bunch of random fields I would set that up as an account_attribute. The table would include a reference key to the original account, a field that specifies what type of attribute it is, and the value of the attribute.

Alternatively, you could have them each in a series of tables. If each country has a different style zip code, for example, a zip code table might not be a bad idea. Just make sure you have a zipcode_type field.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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