>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,
>
Hi Walter,
Tough call indeed. I ran a query against information schema. I can see that if there is 'npi' in the name, then we mostly use char and varchar columns. There are a few that use BIGINT, a few using INT (!), two using NCHAR or NVARCHAR. The length also varies, so no consistency here too. The DB correct way seems to be CHAR(10), though. We do use that same column named differently in a couple of tables - in that case we use BIGINT for that column.
UPDATE. I found that 20 tables use CHAR, 40 use VARCHAR, 14 use BIGINT.
Thanks.
If it's not broken, fix it until it is.
My Blog