Search code examples
database-designrelational-databasedatabase-normalization

Using IDs from multiple tables in a single column

The system rules:

  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.

One of my co-workers created this:

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

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.

That is a poor design and I proposed the following:

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

This is a properly normalized schema and properly enforces relationships and data integrity, while honoring the business rules.

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, what is a reference to a specific database design article / paper to back up my position?

Solution

  • 1NF

    Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).

    http://en.wikipedia.org/wiki/First_normal_form#1NF_tables_as_representations_of_relations

    The simplest way to solve your problem is to introduce "default" division for each department, which simply means "the whole department". After that simply link all articles to Divisions. Maybe something like this (DepartmentDivisionNo = 0 means whole department):

    enter image description here