Is this a ridiculous way to structure a DB schema, or am I completely missing something?

Question

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...

Answer 1

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.

Answer 2

I don't think that either a WTF is warranted or that the guy is doing any mad genius kind of design - it's pretty standard database normalization.

The reason for the department table is that if you don't put the departments into a separate table you will have to deal with users in "Sales", "sales", "Salesmen", "Sails", and "Selling" departments, unless you do something to prevent it. And having the extra table is (part of) the best way I know to do that.

Whether there should be a UserDepartment table is a tougher call, which of course means neither decision is way out and crazy. On the one hand it's a pain when all your table design and logic had assumed one department per user and then that changes, on the other hand doing an extra join for no reason for years and years is a real possibility and also a pain.

I personally would agree with you that the UserDepartment table is probably overkill. Even if it's included, the chances are that over time people will write queries that assume there is only one user per department, so you will end up with the worst of both worlds - an extra join for no reason prior to needing the table, and code not working anyway once more than one department per user becomes allowed.

EDIT - A key driver of whether the many to many relationship should be supported is if the business rules are clear. If you have no idea how a user in multiple departments would even work there isn't much point in adding the table, since your code can't possibly correctly handle the cases where a user is in multiple departments.

Imagine that you did allow many departments per user, just in case. You then implemented a business rule for assigning commissions, based on department. Then multiple departments were allowed. Fortunately, you also had the foresight to write your commission code in a way that took this into account. Unfortunately, you added the commissions from each department for users in both. Management wanted you to base in on the persons role for each sale. So, how much good was having the table in advance? What about the other tables you had "just in case" that are never needed at all?

LATER EDIT - Another reason the consultant might have wanted to add all those intermediary tables is addressed in this follow-up question, the answers to which give some reasons why refactoring a database is usually harder than refactoring code, which would tend to push you towards the "put in all the tables you might ever need" approach.

Answer 3

If the requirement is to have multiple departments per user, this design makes sense. The only gripe about it is the UserDepartmentTable having a surrogate key UserDepartmentID that is not needed (just make the UserId and DepartmentId a composite primary key).

If a user only ever belongs to a single department, your design makes sense (though a department lookup table would still be a good thing).

Answer 4

Some requirements are not clear in your question. The correct answer depends on what your customer wants - If I were you, I would ask the customer about this:

0-What is the difference between a user and an employee?

1-Assuming an employee=user, what if an employee changes departments?

2-Can a group of employees make 1 request?

3-Could an employee belong to more than one department? What about the CEO

4-Is there a subset of employees that are allowed to make requests?

5-What happens to the request when an employee record is deleted (if ever)?

6-Could you delete a request? What happens when the request is delted (make sure you don't delete the employee record by RI)

7-Can the employee make the "same" request more than one time (define the "same")

8-How to handle requests for employees when they leave the company (cancel their requests or delete the requests?)

There may be more questions, but my point is that the solution depends on exact requirements and project scope. Once that is determined the schema can be derived directly. Accordingly both solutions presented may be correct.

Answer 5

I'd like to add a couple point form notes talking explicitly about some of the potential advantages of using a join table in the fashion that your highly-paid consultant did.

• Properly indexed (e.g., if UserDepartmentTable indexes the two foreign keys), there is only a small performance loss of a join table like this due to the foreign keys not being unique. If the foreign keys are guaranteed to be unique, the by the little database theory I know, looking up UserDepartmentTable.Department is no "harder" than looking up any other column in the User table.
• The join table gives you more flexibility to set up other information about the association between the user and the department (e.g., timestamps on creation).
• The join table allows you to "version" the association fairly easily (e.g., when a user changes departments, trigger some index boolean flag like UserDepartmentTable.Active to false and create a new association that is active). It's also possible to have department association versioning with the two-table model (just User and Department), but it's tougher and necessitates adding at least one more column or doing database acrobatics in order to avoid duplicating primary keys.
• It allows you to assign one-to-many or many-to-one or many-to-many associations pretty easily.

That being said, there are several reasons NOT to do what your highly-paid consultant did.

• All the above benefits are all anticipatory of possible future needs, overcomplicating things for the present day. It is not YAGNI-compliant. It's of trivial difficulty later to write a migration that moves from your two-table model to a join table model. You can do that when the business need emerges. To do it before that can be confusing.
• It confuses other developers. While, yes, I would say the expectation for a web developer of your stature (where you're reviewing consultants' decisions) would be to understand and recognize a join table, it's still more complicated than necessary and considering the lack of business need, it causes confusion.

Answer 6

Without full structure of needed information I can't say is it terrible or not. But at least shown piece is not of "WTF" designs. It's just seems as 3-rd Normal Form of data-structure (well, we have theoretically also 4-rd and 5-th also)

Some talks can have place for UserDepartmentTable between two schools of "natural" and "artificial" keys in shown piece. Nothing more, as I can see

Normalization is rule of good DB-developer/designer for a lot of reasons, *de*normalizations are used sometimes in the middle of developments for speed-win mostly