So I am constructing a new structure for a business that does international business. The goal is to store country-specific identification numbers (generally gov't related) by user. For example VAT registrations in the EU or Social Security is the US. Governements generally issue IDs for personal use, business use, etc.

The ideas that I have heard so far are these:

1. Make separate tables for every type of number/ID. For example, there would be a VAT Registrations table and possibly a US Tax ID table (SSN, EIN, etc.).

2. Make a general Tax ID table. This would contain a types table saying what kind of number each record is. It would relate each ID to a user and a country. For example someone could be VAT Registered in every European Union Country and possibly in the US as a business.

The goal is to store everything temporally (start and end date) and have the ability for each user able to have multiple IDs in multiple countries which both methods seem to accomplish. Retrieving the data quickly is most important. There are about 500,000 users (maybe half are active) that will have data. Which method would you go with and why? Thanks in advance for any ideas/input.

I'd go with an id table. Something like:

In a code table, you could store the descriptions of the types, i.e: ssn, vat, etc...
Dave

I think you and I are on the same train of thought. Here is what I came up with after talking to the team and getting their input. This would generally be used as a place to store Tax IDs, but any ID could be stored here. We even played with the idea of keeping names in here but decided that temporal names should be kept somewhere else if only for the sake of readability.

user_identification_numbers
user_identification_number_id
user_id
country_id **country here instead of on the types for cases like VAT
user_identification_number_type_id
user_identification_number
validated **true or false (web validation or possibly by hand)
validated_timestamp **when validation happened
start_timestamp **start of the validity of this id
end_timestamp **end of the validity of this id
entered_by **who put the id in

user_identification_number_types
user_identification_number_type_id
user_identification_number_type_name
user_identification_number_typedescription

**This table will ultimately be replaced by a rules engine in development which explains the country_id appearing here AND in the user_identification_numbers table. Names are also stored temporally with their purpose which would relate to user_identification_numbers by date range.
country_user_identification_number_type_xref
country_user_identification_number_type_xref_id
country_id
user_identification_number_type_id
sequence