Want your opinion: ID vs Number

[Ronald S. Cook]

I have tables in my database-based Web app like Player, Club, and
Tournament.



In each, I have an identity primary key column "ID" (1, 2, 3, 4.). I also
have a column "Number" which is a char(6) to contain the player, club, or
tournament 6-digit number (e.g. 493192). This value will be visible in the
site and used by the user (i.e. not a hidden key behind the scenes).



The only thing I'm not crazy about is that the ID value (while
behind-the-scenes), does appear in the URL at times (e.g.
mysite.com/player.aspx?ID=17). While I could use the Number value instead,
I don't believe this is the most proper way to do it (i.e. using keys is
more appropriate).



I know it's not a big deal for users to see ID keys passed around, but I'd
prefer they didn't.



I've heard the suggestion to do away with the Number column and seed the ID
column to begin at 100001, but I think this is bad design. If, for example,
a user had "666" in there ID and demanded it be changed, then propagating
the value becomes an issue. I know cascading updates aren't a big deal, I
just think its bad design. The PK is supposed to be an arbitrary unique
identifier to be used behind the scenes.



Any opinions on this?



Thanks,

Ron

 

[Marc Gravell]

I'd agree with your viewpoint; primary-keys are part of the database, not
part of the system being modelled; if it was externally facing, I would (by
preference) use the number on anything visible.

...does appear in the URL at times (e.g. mysite.com/player.aspx?ID=17...

Well, why? If you own the system... if you don't like it, improve it ;-p

(i.e. using keys is more appropriate).

It is certainly more appropriate for database code; for a QS, you could go
in any direction; as long as it is indexed, you can switch between the two
easily enough. For viewstate (which isn't so visible) I'd happily use IDs;
I'm also reasonably OK with them on forms, but I don't really like them on a
QS...

That's my £0.02, anyhow...

Marc

 

[Paul E Collins]

[This isn't a C# question, so you might get some more informed
responses by posting to a database-specific group.]

The only thing I'm not crazy about is that the ID value
(while behind-the-scenes), does appear in the URL at
times [...] While I could use the Number value instead,
I don't believe this is the most proper way to do it (i.e.
using keys is more appropriate).

AFAIK, the only benefit of using a "hidden" PK is that it's guaranteed
to be unique, since they're system-generated. It seems redundant if
the "display number" is also unique (not that I know how your clubs or
tournaments work) - but on the other hand some changes in
administration might make them non-unique at some point in the future,
if (say) clubs start being identified by the combination of number and
US state; and then you'd have a headache.

The PK is supposed to be an arbitrary unique identifier
to be used behind the scenes.

Says who? You can use it however and wherever you like.

Eq.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

In each, I have an identity primary key column "ID" (1, 2, 3, 4.). I also
have a column "Number" which is a char(6) to contain the player, club, or
tournament 6-digit number (e.g. 493192). This value will be visible in
the site and used by the user (i.e. not a hidden key behind the scenes).

That's fine, mark the ID (PK) as identity if possible.

The only thing I'm not crazy about is that the ID value (while
behind-the-scenes), does appear in the URL at times (e.g.
mysite.com/player.aspx?ID=17). While I could use the Number value
instead, I don't believe this is the most proper way to do it (i.e. using
keys is more appropriate).

Agree again. you should use the PK for these cases. Beware this can cause a
security problem, if lets say you let the own athlete edit its profile he
could realize the ID part and change it to access another athlete's info. In
this case it's better to store this info in session

I know it's not a big deal for users to see ID keys passed around, but I'd
prefer they didn't.

I see no problem with that, as a matter of fact you should always use keys
to uniquely identify a record.

I've heard the suggestion to do away with the Number column and seed the
ID column to begin at 100001, but I think this is bad design. If, for
example, a user had "666" in there ID and demanded it be changed, then
propagating the value becomes an issue. I know cascading updates aren't a
big deal, I just think its bad design. The PK is supposed to be an
arbitrary unique identifier to be used behind the scenes.

Just keep it like it's right now.