Using IDs from multiple tables in a single column

Question

One of my co-workers created a schema similar to the following. This is a simplified schema including only the parts necessary to address this question.

The system rules are as follows:

1. Departments can have 0 to many divisions.
2. A division must belong to only one department.
3. An article can be assigned to either a department, or a division of that department.

The schema is:

Department
---------- 
DepartmentID (PK) int NOT NULL
DepartmentName varchar(50) NOT NULL

Division
--------
DivisionID (PK) int NOT NULL
DepartmentID (FK) int NOT NULL
DivisonName varchar(50) NOT NULL

Article
-------
ArticleID (PK) int NOT NULL
UniqueID int NOT NULL
ArticleName varchar(50) NOT NULL

He defined the schema using an imaginary rule (for lack of a better term), that all DepartmentIDs would be between 1 and 100, and all DivisionIDs would be between 101 and 200. He states that when querying the Article table, you will know whether the UniqueID is from the Department table or the Division table based on what range it falls into.

I think this is a poor design and proposed the following alternative schema:

Department
----------
DepartmentID (PK) int NOT NULL
ParentDepartmentID (FK) int NULL /* Self-referencing foreign key.  Divisions have parent departments. */
DepartmentName varchar(50) NOT NULL

Article
-------
ArticleID (PK) int NOT NULL
DepartmentID (FK) int NOT NULL
ArticleName varchar(50) NOT NULL

I believe this is a properly normalized schema and properly enforces relationships and data integrity, while honoring the business rules outlined above.

My specific question is this:

I know using one column to contain values from two domains is poor design, and I can argue the benefits of the foreign key in the Article table. However, can someone provide a reference to a specific database design article / paper that I can use to backup my position. If I can point to something concrete, it will make it that much easier.

Answer 1

Your self-reference could introduce a hierarchy of divisions within divisions. You need code (usually a trigger) to ensure that no divisions allow this.

The original schema using encoding is broken too. You have no enforceable FK

Personally, I'd consider this with a check constraint to ensure than only one of DepartmentID and DivisionID per row is populated

Article
-------
ArticleID (PK) int NOT NULL
DepartmentID (FK) int NULL
DivisionID (FK) int NULL
ArticleName varchar(50) NOT NULL

Why?

• no code needed
• no nested divisions
• full DRI via FKs to parent table
• enforced "multiple parent, only one at a time"

You could also use computed columns to add a column that says "department" or "division"

Another way: mandate that each department has at least one division...

Answer 2

Might I propose a new idea? The lookup table here is an auto-increment, in this way there is only ONE ID used between both "Division" and "Departments". This way also allows for "Departments" to be owned by multiple divisions (I think this is what you are after).

Whenever I have a set of unique tables that need to be shared with only one item, I do this "Lookup" table design. If there are any contradictions to this design, I would really like to hear them because this scenario comes up fairly often for me!

I use access to quickly layout my DBs, then usually convert them to MySQL...

EDIT: Since there is some misunderstanding with my layout, let me explain it in detail.

With this design an Article can be owned by either A (single) Division, OR a single department. Not both department and a devision, but if you assign it to a department you can then know which division the department is assigned to by querying both division and department tables.

This design also keeps "Division" and "Department" normalized in case additional information is tied to either one in the future.

I reviewed the requirements and still stand by this design.