Use IDs or strings?

[Database Administrator]

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?

[Database Administrator]

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

[Database Administrator]

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

[Database Administrator]

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?"

[Database Administrator]

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

[Database Administrator]

>> 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.