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:
UserID (primary Key, indexed, no dupes) FirstName LastName Department
RequestID (primary Key, indexed, no dupes) <...> various data fields containing request details UserID -- foreign key associated with User table
Consultant designed it like this (with sample data):
UserID FirstName LastName 234 John Doe 516 Jane Doe 123 Foo Bar
DepartmentID Name 1 Sales 2 HR 3 IT
UserDepartmentID UserID Department 1 234 2 2 516 2 3 123 1
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...