Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: Data Organization: 2 Business Cases

    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.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I'd go with an id table. Something like:

    Code:
    userid
    seq no  --increases by 1 for each id that a single user has. this way a user 
                 can have 1 to n ids
    id_type
    id_value
    start date
    end date
    In a code table, you could store the descriptions of the types, i.e: ssn, vat, etc...
    Dave

  3. #3
    Join Date
    Mar 2010
    Posts
    2

    Thanks!

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •