Is this a ridiculous way to structure a DB schema, or am I completely missing something?
- by Jim
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...
