Well, that is a tough call. Consistency within your database is important, so it definately is not a clear cut.

In our system each table has a integer PK. For a healthcare provider NPI, I'd use a character column , as an alternate key.

But then again, if your database is very consistent in using a char(10) as PK, I'd be inclined to do the same.

Walter,


>>Is this a surrogate key? (i.e. system-assigned, no meaning, tied to another business identifier)
>>
>>If it's a surrogate primary key, I'd ask someone to make a compelling argument on why a char(10) is needed. The answer of, "the other tables use char(10)'" doesn't cut it (at least not for me personally)
>
>Hi Kevin,
>
>It is the identifier but not our system's made. This is the The National Provider Identifier (NPI). The NPI is a 10-position, intelligence-free numeric identifier (10-digit number). This means that the numbers do not carry other information about healthcare providers, such as the state in which they live or their medical specialty. The NPI must be used in lieu of legacy provider identifiers in the HIPAA standards transactions.
>
>The problem is - I need to make a good argument if I'm going to stick with BIGINT. We do use BIGINT in several of the tables, but in those tables we name this column differently for some reason. If we name it using NPI as part of the naming convention, then most of the existing tables use either char or varchar data type. Since we're not going to change all these already used tables, the main question now - should the new tables follow the old 'standards' or should they use new standards?
>
>One of the most important arguments my colleague is making is about implicit conversion. So I'm not sure if I should try to defend the BIGINT idea or just go with what they suggest. If the former, I need to make my argument rock solid.
>
>Thanks again.