Naming Tables and Views – Standard Naming Conventions to Follow

But how far can I continue to break the information down into smaller lookup tables before it becomes unmanageable? I could create a Gender table and have a 1 correspond to Male and a 2 correspond to Female in a separate lookup table.

You're mixing two different issues. One issue is the use of a "lookup" table; the other is the use of surrogate keys (id numbers).

Start with this table.

ID  LName   FName   Gender  Position
1   Doe     John    Male    Manager
2   Doe     Jane    Female  Sales
3   Smith   John    Male    Sales

You can create a "lookup" table for positions like this.

create table positions (
  pos_name varchar(10) primary key
);

insert into positions
select distinct position 
from employees;

alter table employees
add constraint emp_fk1
foreign key (position) 
  references positions (pos_name);

Your original table looks exactly like it did before creating the "lookup" table. And the table of employees requires no additional joins to get useful, human-readable data out of it.

Using a "lookup" table boils down to this: Does your application need the control over input values that a foreign key reference provides? If so, then you can always use a "lookup" table. (Regardless of whether it uses a surrogate key.)

In some cases, you'll be able to completely populate that table at design time. In other cases, users need to be able to add rows to that table at run time. (And you'll probably need to include some administrative processes to review new data.) Gender, which actually has an ISO standard, can be completely populated at design time. Street names for international online product orders probably have to be added at run time.

1. Pick a standard, make sure it makes sense, and document it. There have been plenty of debates about this (e.g. whether the identity column in the contacts table should be called ID or ContactID, or whether the contacts table should be called dbo.Contacts or the unnecessarily verbose dbo.tblContacts), and you're never going to find agreement among your peers. Which standard you choose is not important; what is important is that you use it consistently, make it well-known, and get agreement from your colleagues that this is the way.

2. Your columns should be self-documenting (and not cryptic, abbreviated or obfuscated) where practical. I would NOT rely on the extended properties, but that's just me (google/bing for problems with extended properties and you will see plenty of complaints I would guess). External documentation / data dictionary is much more valuable to me - if your database goes south and so does your backup, it makes it that much easier to rebuild. It also makes it easier to describe your schema to junior developers etc. without giving them direct access to everything.