Thread: When to use name verses id

I got some very helpful suggestions last time I posted and have reworked my
entire db as a result.

I did away with a bunch of tbName for tables type stuff and a ton of pkName
and fkName for key columns. Thanks for the help and the design actually looks
a lot cleaner now.

My situation and question is now this:

I have some columns that I want to make sure have valid names in them.
For example one column refers to a foreign key which is a table containing
valid privilage access names like admin, user, agent, etc.... This
access_privilage table also has a serial column.

I guess my question is this. Should I _always_ refer to a serial or id type
of field or is there an appropriate time to refer to a name that the id
points to?

Do I even need an id column if I make the name column unique?

I used the suggestion made earlier when I needed a table to contain unique
names but each name needed multiple rows, That table only uses serial id's of
the referenced tables. I see the logic here because this table is "internel"
so to speak and won't be searched or reported on directly. These other tables
will be. Am I making sense here?
--
Lewis Bergman
Texas Communications
4309 Maple St.
Abilene, TX 79602-8044
915-695-6962 ext 115

Lewis Bergman <lbergman@abi.tconline.net> writes:
> Do I even need an id column if I make the name column unique?

You could dispense with an ID column (and IMHO should do so) if you can
*guarantee* that the name column will always be unique.  In a lot of
practical situations that falls down --- eg, you'd be foolish to assume
that a company will never have two employees named Tom Lane.

The main reason people tend to use arbitrarily-assigned ID values is so
they can be certain of having a unique primary key for the table, even
when the other identifying info turns out to be less unique than it
might at first seem.

Integer IDs are probably also more compact and faster to compare than
strings, but this effect is not so strong that it should govern your
decisions.  If you don't really need an ID column, I think you're
better off without one.

            regards, tom lane

Lewis,

> Integer IDs are probably also more compact and faster to compare than
> strings, but this effect is not so strong that it should govern your
> decisions.  If you don't really need an ID column, I think you're
> better off without one.

A number of database books have long discussions on ID columns, keys,
 and how to effeciently identify unique rows.  In fact, half the DB and
 SQL books on either book page (from www.postgresql.org or from
 techdocs.postgresql.org) have such chapters.

Make sure that if you are going to rely on a name column as your unique
 value, that you make it your table's Primary Key or at least put a
 Unique Index on it.  Tables without primary keys are disasters in the
 making ...

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco