I have done a fair bit of work with relational databases, and think I understand the basic concepts of good schema design pretty well. I recently was tasked with taking over a project where the DB was designed by a highly-paid consultant. Please let me know if my gut intinct - "WTF??!?" - is warranted, or is this guy such a genius that he's operating out of my realm?
DB in question is an in-house app used to enter requests from employees. Just looking at a small section of it, you have information on the users, and information on the request being made. I would design this like so:
User table:
UserID (primary Key, indexed, no dupes)
FirstName
LastName
Department

Request table
RequestID (primary Key, indexed, no dupes)
<...> various data fields containing request details
UserID -- foreign key associated with User table

Simple, right?
Consultant designed it like this (with sample data):
UsersTable
UserID  FirstName   LastName
234     John        Doe
516     Jane        Doe
123     Foo         Bar

DepartmentsTable
DepartmentID   Name
1              Sales
2              HR
3              IT

UserDepartmentTable
UserDepartmentID   UserID   Department
1                  234      2
2                  516      2
3                  123      1

RequestTable
RequestID   UserID   <...>
1           516      blah
2           516      blah
3           234      blah

The entire database is constructed like this, with every piece of data encapsulated in its own table, with numeric IDs linking everything together. Apparently the consultant had read about OLAP and wanted the 'speed of integer lookups'
He also has a large number of stored procedures to cross reference all of these tables.
Is this valid design for a small to mid-sized SQL DB?
Thanks for comments/answers...

Makes perfect sense to me.  It's just very normalized, which imparts a lot of flexibility that you wouldn't have otherwise.  De-normalized data is a pain in the butt.