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