Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
1  
While I do not believe there are any nested divisions at the moment, I suppose there could be. Having a hierarchy provides the flexibility to expand in the future. If you have both the DepartmentID and DivisionID in the Article table, doesn't it present a possibility to have a DivisionID that does not belong to the same department as the DepartmentID value? Wouldn't code be required to prevent that? –  NYSystemsAnalyst Aug 3 '11 at 16:01
1  
@NYSystemsAnalyst: see where I said " a check constraint to ensure than only one of DepartmentID and DivisionID per row is populated" So both are never set –  gbn Aug 3 '11 at 16:06
    
Sorry, missed that. –  NYSystemsAnalyst Aug 3 '11 at 16:07
    
@NYSystemsAnalyst: ...this is also declarative at the table definition level. No code needed. –  gbn Aug 3 '11 at 16:09
1  
@CenterOrbit: probably not: that isn't a requirement yet. If asked, I'd consider a subtype/superkey approach for the company hierarchy and hang articles off the top table. –  gbn Aug 6 '11 at 5:57

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!

enter image description here

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.

share|improve this answer
1  
This is worse than OP's UniqueID column but with an extra table and the limit of one article per department/division only if department/division have the same number. The ID in Lookup can not have multiple parents if you want an FK. This is allowed at design time because you have no data. –  gbn Aug 3 '11 at 16:27
    
@gbn : I dont see how this is worse. If an article is assigned to a "Division" and you wanted to know what "Department" it falls under, you could easily look that up through an easy SQL statement checking for that name. Also it does not limit one article per department/division, the more the Article table grows they would tie to many Lookups (department/division) ID's –  CenterOrbit Aug 3 '11 at 17:03
    
Add some rows to the tables and try it. When you put DivisionID = 123 into Lookup, you require a row DepartmentID = 123 too so the foreign key doesn't fail. Then read my first comment again... –  gbn Aug 3 '11 at 17:08
    
@gbn : I did as you had requested and the errors you are predicting did not happen. But I now understand what your argument is. The reason why it doesnt give me errors is because of the join properties between these tables is set to "1: Only include rows where the joined fields from both tables are equal" . This means that only ID's that are in "Lookup" can be in either "Division" or "Department" (or neither) without breaking the rule. If you would like screenshots or the example access DB file, I can get it to you (if your interested) –  CenterOrbit Aug 3 '11 at 17:34
1  
So, no FKs again. How does this differ from the original solution except we have a hanging lookup table with one column that does...what? But you can now have multiple articles per parent Lookup row. No added value, no point. –  gbn Aug 3 '11 at 19:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.