Use IDs or strings?

On Oct 26, 4:58 pm, Victor Porton wrote:
> I design a DB which contains districts of cities.
>
> The question: Is it reasonable to store IDs of districts instead of
> district names, provided that we have hard-coded map from district IDs
> to district names? Conditions: 1. Disk space is not an issue. 2.
> Search on districts is not going to be implemented.
>
> I'm sure that disk space and ability to quickly search districts
> mandate to use IDs in place of strings. But that's is not an issue.
>
> So, should I?

I usually stick to the natural keys when possible. Now some questions
are:

Are these IDs natural? (e.g. plot map lot numbers)
Are these IDs known to users?
If the answer to either of these is yes, then use the IDs.

Are you merely "normalizing" the verbiage out to reduce space? What
does you data model look like? Are you planning on leaving the
district names totally out of the database?

Your search comment is totally wrong for many DBMS products. Numeric
ID keys are NOT significantly faster than text keys. And with the
prices and capacities of disc drives nowadays, trying to squeeze disc
space is a poor planning approach. Before thinking about space or
speed, get the system functioning first.

Ed
 >> Stay informed about: Use IDs or strings? 

On Oct 26, 10:44 pm, Jasen Betts wrote:
> On 2008-10-26, Victor Porton wrote:
>
> > I design a DB which contains districts of cities.
>
> > The question: Is it reasonable to store IDs of districts instead of
> > district names, provided that we have hard-coded map from district IDs
> > to district names? Conditions: 1. Disk space is not an issue. 2.
> > Search on districts is not going to be implemented.
>
> > I'm sure that disk space and ability to quickly search districts
> > mandate to use IDs in place of strings. But that's is not an issue.
>
> if it's not going to be a foreign key for some other reason there's no
> need make it one.
>
> Bye.
>    Jasen

Respectfully, I'm forced to disagree with Jasen. Proper database
design would mandate to use of a lookup table (using an ID) for an
item such as Vistor describes. The ONLY exception to this being if
the master table (that would refer to these ID's) is expected to
contain VERY few rows.

With the exception of tables containing very few rows, it is generally
better to use proper database design regardless of scale, since scale
can often expand unexpectedly. Often an oversite such as this
continues to go unnoticed and unfixed until the tables size has grown
completely unwieldy and a fix would require taking down a huge and
frequently used database.

Here are a few of the reasons to build those districts into a lookup
table now:

1) Reusability: The list of districts can be referenced by multiple
table, or even exported and used in another (perhaps similar)
database.
2) Updates: City designs are not always static. Say the district now
called "East Side" is renamed "Smithtown." Proper database design
(using a lookup table) means you only need to change 1 row of data.
3) Searching is NOT hindered: In fact it might even work better
depending on your DB engine and query design. Regardless, you can
still search on a district name using an easily written join
statement.
4) Searching on other fields is enhanced: When the master table grows
to contain thousands of rows, the DB engine will be able to return
results much faster when the rows are smaller.
5) Concurrency (sp?): When this table is queried by thousands of
users, simultaneously, you'll which you used that lookup table.

Code Happy!

Christopher J Smith
BeWise Consulting Group
 >> Stay informed about: Use IDs or strings? 

Ed Prochak wrote:

> Your search comment is totally wrong for many DBMS products. Numeric
> ID keys are NOT significantly faster than text keys. And with the
> prices and capacities of disc drives nowadays, trying to squeeze disc
> space is a poor planning approach.

Another approach when in doubt of using surrogate keys, would be to ask
oneself: "how many million districts will there ever be? how many times
per day will a district change its name?"
 >> Stay informed about: Use IDs or strings? 

>> I design a DB which contains districts of cities. <<

Did you research the identifiers used by the Census or other
government agencies? That will be your identifier and the district
name will be the description.
 >> Stay informed about: Use IDs or strings? 

Ed Prochak wrote:
....
> Are these IDs known to users?
> If the answer to either of these is yes, then use the IDs.
> ...


If they are useful (pertinent if you like), they will become known. The
only decision to make about such ID's is whether they have a pertinent
use in one of the applications.
 >> Stay informed about: Use IDs or strings? 

"paul c" wrote in message

> Ed Prochak wrote:
> ...
>> Are these IDs known to users?
>> If the answer to either of these is yes, then use the IDs.
>> ...
>
>
> If they are useful (pertinent if you like), they will become known. The
> only decision to make about such ID's is whether they have a pertinent use
> in one of the applications.

PMFJI. Applications can be seen as "users of the database". This extends
the meaning of the word "user" beyond what most people in the group use it
to mean.
 >> Stay informed about: Use IDs or strings? 

Walter Mitty wrote:

>> If they are useful (pertinent if you like), they will become known. The
>> only decision to make about such ID's is whether they have a pertinent use
>> in one of the applications.
>
> PMFJI. Applications can be seen as "users of the database". This extends
> the meaning of the word "user" beyond what most people in the group use it
> to mean.

Plainly, no. This is clearly a semantic gimmick
 >> Stay informed about: Use IDs or strings? 

Marco Mariani wrote:

>> PMFJI. Applications can be seen as "users of the database". This
>> extends the meaning of the word "user" beyond what most people in the
>> group use it to mean.
>
> Plainly, no. This is clearly a semantic gimmick

I re-state: when somebody says "the primary key must/mustn't have
meaning to the users", i'm pretty sure he is

1) opening a can of worms

2) referring to people

otherwise, the discussion loses any sense.
 >> Stay informed about: Use IDs or strings? 

On Dec 2, 5:17 am, Marco Mariani wrote:
> Marco Mariani wrote:
> >> PMFJI.  Applications can be seen as "users of the database".  This
> >> extends the meaning of the word "user" beyond what most people in the
> >> group use it to mean.
>
> > Plainly, no. This is clearly a semantic gimmick
>
> I re-state: when somebody says "the primary key must/mustn't have
> meaning to the users", i'm pretty sure he is
>
>   1) opening a can of worms
>
>   2) referring to people
>
> otherwise, the discussion loses any sense.

2) is correct for what I meant as Users. And I agree that any other
use does not make sense. Applications are not users.
ed
 >> Stay informed about: Use IDs or strings? 

On Dec 1, 10:07 am, paul c wrote:
> Ed Prochak wrote:
>
> ...
>
> > Are these IDs known to users?
> >     If the answer to either of these is yes, then use the IDs.
> > ...
>
> If they are useful (pertinent if you like), they will become known.  The
> only decision to make about such ID's is whether they have a pertinent
> use in one of the applications.

Applications are not the users I was referring to. Applications (code)
are part of the system and must therefore have access to such ID's.
But that's not relevant to the original question.
ed
 >> Stay informed about: Use IDs or strings? 

Ed Prochak wrote:
> On Dec 1, 10:07 am, paul c wrote:
>> Ed Prochak wrote:
>>
>> ...
>>
>>> Are these IDs known to users?
>>> If the answer to either of these is yes, then use the IDs.
>>> ...
>> If they are useful (pertinent if you like), they will become known. The
>> only decision to make about such ID's is whether they have a pertinent
>> use in one of the applications.
>
> Applications are not the users I was referring to. Applications (code)
> are part of the system and must therefore have access to such ID's.
> But that's not relevant to the original question.
> ed

Alright, to be pedantic, if they are useful to users they will become
known by users.
 >> Stay informed about: Use IDs or strings? 

"paul c" wrote in message

> Ed Prochak wrote:
>> On Dec 1, 10:07 am, paul c wrote:
>>> Ed Prochak wrote:
>>>
>>> ...
>>>
>>>> Are these IDs known to users?
>>>> If the answer to either of these is yes, then use the IDs.
>>>> ...
>>> If they are useful (pertinent if you like), they will become known. The
>>> only decision to make about such ID's is whether they have a pertinent
>>> use in one of the applications.
>>
>> Applications are not the users I was referring to. Applications (code)
>> are part of the system and must therefore have access to such ID's.
>> But that's not relevant to the original question.
>> ed
>
> Alright, to be pedantic, if they are useful to users they will become
> known by users.

All right, the tribe has spoken. The word "users" is to be reserved for
people. So be it.

However the question of whether Applications code must have access to
internal IDs is not as automatic as the above requests indicate.

Particularly is more than one application uses data from a single database.
If the ONLY purpose behind ID's is to join foreign keys with the keys they
reference,
then a view performs the join need not include the ID in the view columns.
If an application only uses the the view, and doesn't have access to the
underlying tables, then the ID column, and the foreign key are effectively
hidden from it.

Even in a situation where the ID is revealed to the appliction, the
application will generally have to maintain the relationship between user
visible keys and ID fields.
Example: in a job seeker database, the JobSeekerID was generally hidden
from the users (except for certain administrative users). The users used
SSN to identify Job seekers (with dummy SSNs for job seekers with no SSN or
with an unknown SSN). A certain job seeker was enetered in the system with
the wrong SSN (a typo). Later, a correct entry was made with the right
SSN, and a different ID, of course. Straightening out this mess required
manual intervention.
 >> Stay informed about: Use IDs or strings? 

On Dec 3, 1:23 pm, "Walter Mitty" wrote:
> "paul c" wrote in message
>
>
>
>
>
> > Ed Prochak wrote:
> >> On Dec 1, 10:07 am, paul c wrote:
> >>> Ed Prochak wrote:
>
> >>> ...
>
> >>>> Are these IDs known to users?
> >>>>     If the answer to either of these is yes, then use the IDs.
> >>>> ...
> >>> If they are useful (pertinent if you like), they will become known.  The
> >>> only decision to make about such ID's is whether they have a pertinent
> >>> use in one of the applications.
>
> >> Applications are not the users I was referring to. Applications (code)
> >> are part of the system and must therefore have access to such ID's.
> >> But that's not relevant to the original question.
> >> ed
>
> > Alright, to be pedantic, if they are useful to users they will become
> > known by users.
>
> All right, the tribe has spoken.  The word "users" is to be reserved for
> people.  So be it.
>
> However the question of whether Applications code must have access to
> internal IDs is not as automatic as the above requests indicate.
>
> Particularly is more than one application uses data from a single database.
> If the ONLY purpose behind ID's is to join  foreign keys with the keys they
> reference,
> then a view performs the join need not include the ID in the view columns..
> If an application only uses the the view, and doesn't have access to the
> underlying tables, then the ID column, and the foreign key are effectively
> hidden from it.
>
> Even in a situation where the ID is revealed to the appliction,  the
> application will generally have to maintain the relationship between user
> visible keys and ID fields.

relational integrity. that's what a good DBMS should be able to do.

> Example:  in a job seeker database, the JobSeekerID was generally hidden
> from the users  (except for certain administrative users).  The users used
> SSN to identify Job seekers  (with dummy SSNs for job seekers with no SSN or
> with an unknown SSN).  A certain job seeker was enetered in the system with
> the wrong SSN  (a typo).  Later, a correct entry was made with the right
> SSN, and a different ID, of course.  Straightening out this mess required
> manual intervention.

Fixing SSNs is messy, even when done right. (lots of legal issues),
but SSN IS NOT a surrogate ID. It is not the kind of ID I was
discussing. You really gave a poor example.

Internal ID's are exactly that: INTERNAL to the DATABASE. Even
"Administrative users" should not see these IDs. code and developers
should be the range of their exposure.

The fact that all too many systems expose these IDs is not
justification for making this error in new systems.

Now there are cases where an ID does become useful. If that is the
case, then the ID should be designed with certain data integrity built
in, e.g., a check digit. It is certainly easier to use a VIN or SSN to
identify a vehicle or a person. The VIN is a good example because it
is basically a summary of key attributes of a vehicle, encoded in a
standard way. Otherwise the PK of a vehicle table becomes a composite
key of Manufacturer, Model, year, etc.
(Hey I may be emphatic, but I am not dogmatic.)

If it is planned to be exposed to users, then do it right. IOW, the ID
in that case should NOT be just s sequence number.

Ed
 >> Stay informed about: Use IDs or strings?