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.