table design and ID field

[Christopher Glaeser]

I've been reviewing Access examples to better understand table design and
have a question regarding small tables with a modest number of unique
contact types or department types or vehicle types, etc. Sometimes, these
tables have both an ID field and text field, while other times the tables
only include the text field (in these examples the strings are always unique
e.g. "sales", "marketing", etc.).

Example with ID
tblContctType
ContactTypeID: number
ContactType: string

Example without ID
tblDepartment
Department: string

What are the pros and cons of each design?

Best,
Christopher

 

[G. Vaught]

In the case of the ContactTypeID: number/somtimes autonumber is just that a
number. It does tell you anything about the ContactType,which would be
Friend, Client, etc. The Id is used just as a placeholder and to set a
primary key. In reality, ContactType could be defined the same as
Department, as long as ContactType would not have any repeating data,
ContactType can be set as a primary key.

If you can't define a primary key from an existing field or combo of fields
(not recommended), you create a autonumber primary key. In the case of a
lookup table, which Department and ContactType would certainly be
classified, I tend to use the one field as the primary key. An separate ID
field is not necessary.

The example is probably showing you acceptable ways to define a primary key.

 

[Gary Schuldt]

Christopher,

here's one perspective you might consider in learning about table design:

The fundamental purpose of a database table is to define attributes of
interest (i.e., fields/columns) about a single type of real-world object.

To take your Department example:

The real-world thing you want to keep information about is Department; that
translates directly into the name of the table: tblDepartment, as you
already indicated.

One of the attributes you want to track (I'm guessing) in the name of the
department, which would translate into DepartmentName: string.

History tells us that, over time, we encounter (or invent) needs for more
and more attributes. Examples:

DepartmentNumber: (probably also) string
DepartmentCharter: string
DepartmentBudget: currency
DepartmentMailStop: string

and so on. Who can predict the future, anyway, except to say that there
will be more, not fewer, attributes of our database objects!

Unfortunately, because these are "real-world" attributes, their values can
change (or be changed) at any time, through no fault of your own. Dept X
might change its name from "Frazzup" to "Frazzemup", just because it sounds
better. But it is still Dept X, only its name has changed. The same could
be true of any of the other attributes (including DepartmentNumber).

Of course, there probably IS a business rule that no two Departments should
have the same Name or Number--i.e., that those values should be Unique. But
that still doesn't say that they can't change.

The point is that you still need as way to say that this is still Dept X,
and that is the role of DepartmentID: autonumber and Primary Key

This is the Identifier, the one thing that will (should) NEVER change,
because it is not a real-world characteristic but one invented by the
database designer to assign a handle to that particular object instance.

The major advantage of the ID is that you can use it to relate the object to
other things. For example, you can record that Employee Y works in
Department X using the following structure:

tblEmployee
EmployeeID:
EmployeeName:
WorksInDepartmentID: number (foreign key)

Suppose you had used DepartmentName to implement this relationship between
Employee and Department. If Department X employed 50 Employees and decided
to change its Name, then you'd have to make 51 updates to the database, even
though there was only one real-world change. However, if instead you'd used
DepartmentID (which never changes), you'd only have to make one database
update (the value of DepartmentName for Department X).

So the perspective here is how the object you're trying to identify will
evolve. If the field you're thinking of using is unique-valued and stable
(unchangeable) for the duration of your application, go ahead and use it as
an identifier. Otherwise, always go the extra distance to create an
"artificial" "internal" identifier that is stable. BTW, no user ever needs
to see a value of this "DepartmentID", since you can always look up the
(current) DepartmentName and display that instead.

Gary

 

[Christopher Glaeser]

So the perspective here is how the object you're trying to identify will

evolve. If the field you're thinking of using is unique-valued and stable
(unchangeable) for the duration of your application, go ahead and use it
as
an identifier. Otherwise, always go the extra distance to create an
"artificial" "internal" identifier that is stable.

Thanks for the detailed explanation. I have one small table in my design
called Status with field values "Opened", "Scheduled", "Inpsected",
"Invoiced", "Closed". Given that the table will likely never change, I
*could* use that field as the identifier, but for the sake of simplicity
through consistancy, and having read your detailed explanation, I'll
include an ID on all my tables. Thanks again for taking the time to help me
understand these design issues.

Best,
Christopher