[AccessD] Names or numbers?

You seem to be stuck in the perennial argument about meaningful vs
meaningless keys.  I agree that keys that are never exposed to the user
should be autonumbers or their equivalent.  I'm not sure I understand
what you mean by "naming details"?  You don't name records, you create
keys for them.  Personally, I prefer meaningless keys, because those are
used internally by the DBMS, not by humans.  They are used to create and
enforce relationships, while some human-friendly value (like an
agreed-upon code) is used to provide something the user can recognize
for other purposes.  If you are working in the tables themselves, you're
the one who is wrong because that is a good way to destroy the integrity
of the data.  Even as a developer, I do NOT create meaningful keys so I
can look at a single table and know exactly what each record refers to.
I guess that means I'm mostly on the contractor's side in this, although
I would go further and use a straight autonumber without any alpha
characters or attempt to make the thing partly meaningful.  As far as
leaving gaps to insert other records, that is a trap because you are
depending on ordering by the meaningful PK.  In fact, with an additional
non-PK user-friendly key, you can order on that and create all the gaps
you want.  

Charlotte Foust


-----Original Message-----
From: Roz Clarke [mailto:roz.clarke at donnslaw.co.uk] 
Sent: Wednesday, August 04, 2004 4:51 AM
To: AccessD (AccessD at databaseadvisors.com)
Subject: [AccessD] Names or numbers?


Dear all

I am currently engaged in an argument with a contractor over the naming
of details in our database.

His argument is that since the actual names of the details will never be
exposed to the ordinary user, we should just give them numbers;
X0000001, X0000002 and so forth. X denotes the current phase of
development, another whacky idea of his. The 'friendly' names are held
in a lookup table.

Personally, since I have to work with these details on a daily basis, I
would like the code to be a combination of categorisation, e.g. using
the 1st two characters to describe the position / type of the detail,
say XC for claimant details and XD for defendant details and XA for
admin details, whilst using the other (up to 6) characters to describe
the detail, say XCDOB for claimant's date of birth.

The contractor's contention is that people will make up arbitrary &
meaningless character codes which will be confusing, whereas the
numbered details will be in a sensible sequence.

a) he is not proposing to leave any gaps in the sequence for later
insertion of related details
b) I don't see how a number is going to be less confusing than an alpha
code
c) we can still use the lookup table with the alpha codes if needed

Has anyone got any thoughts on naming conventions? Any experience of
fully numeric naming systems that they can share? I have the authority
to overrule him but this is a really big project so I want to get it
right, and he is
(theoretically) a lot more experienced than I am. He just hasn't come up
with any convincing arguments.

TIA

Roz