Design review - do you think I'm doing this the right way? First commercial project for me!

Question

I'm tasked with designing an application that will allow a person to scan a legal document, save that associated with a Name and save it to a database.

Now, inside of the Organization, there are many departments, and each department can have many sub departments.

Problem lies in that some larger organizations will have many departments and smallers ones will only have 1 or two.

I've though about creating a Department table and a Supdepartment table to create associations, etc. That way it's extensible and users can dynamically create departments to fit my program to their organizational scheme.

Am I approaching this the right way? As I said, this is my first commercial application so I want to do it right and set a name for myself for delivering things on time and good code for other to expand upon.

Edit: Something like this do you think is better?

Department
ID Name Description ParentDepartment

If ParentDepartment is null then that is a root department, correct? Is this the optimal solution? The design is oriented that way because I want to load buttons on my form according to the number of root departments.

If its set up this way won't I have to scan every single entry in the Department table to verify it's "rootiness"? :P

Answer 1

IMO you may be better off by just having a department table, then have a self-referencing parentId column so top level departments would have null parent, any sub departments would have a parent. This way you can nest however deeply you would like.

Answer 2

Have a single Department table and have self referencing. So if A is a department and it has two sub departments x and y,

DeptNo DeptName ParentDeptID 1 A null 2 x 1 3 y 1

This way, you will not tie the no of levels in this hierarchy to just 2

Answer 3

I have done this in the past, and I feel obligated to warn you that I think you will be better off not identifying documents as belonging to departments. As you say, they change and grow (and I assume shrink) over time, so you have to keep changing your lists. I suspect you will have more luck tagging the docs. For example, this doc has a customerid=12345 and an invoiceid=asdasdasd. Another doc has no customer id, rather it has a paymentid=1234567890. Then each department can decide what it is interested in, and changes won't require changes to the scanned document -- just the retrieval mechanism.

EDIT: I should also have noted that some organizations have temps or lower level folks doing the scanning. If you have to rely on them to correctly categorize a document you might lose or misfile digital documents, just as you would paper documents. Again, perhaps another reason not to link docs strictly to departments. Also, having tags allows a single document to be linked to 2 departments or sub-departments; that alone might be reason enough not to do it your way.

Answer 4

You are talking about flexible design (i.e. easily extendible):

What about departments which have 2 parents? There can be situations where departments are not easily categorized: so adding them to two parents makes them easy to find (think of the situation: Red Green Yellow. Yellow has two parents; Red and Green, which make them easy to find, either by browsing Red or Green. The "redundancy" will, I believe, pay off the better possibilities to correctly categorize departments.)

I think it's better to keep relations between departments in a different table:

Department
 Id Name Description

DepartmentRelation
 ParentId ChildId

This also complies with the 1NF: http://en.wikipedia.org/wiki/First_normal_form

Answer 5

If you are dealing with hierarchical data and using SQL Server 2008, then you may want to consider using the new HierarchyID data type. See Model Your Data Hierarchies With SQL Server 2008 for a full description.

It may be overkill for your scenario but it makes working with node-identifying ordered paths quite easy. e.g. for easily finding the root node for a specific department.