Using IDs from multiple tables in a single column

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:

• Departments can have 0 to many divisions.

• A division must belong to only one department.

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

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

StackExchange Database Administrators Q#4278, answer score: 5