Relationship Scenarios

Question

Relationship Scenarios

TJT

SSCommitted

Points: 1926
More actions
April 26, 2014 at 4:49 am

#396663

I am trying to decide on the best relationship scenario for the following:
Department has one or more Employees
Department has one or more Divisions
Divisions have one or more Units
Units have one or more Groups
Every employee belongs to a Department, Division, Unit, and a Group.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

TJT (4/26/2014)
I am trying to decide on the best relationship scenario for the following:
Department has one or more Employees
Department has one or more Divisions
Divisions have one or more Units
Units have one or more Groups
Every employee belongs to a Department, Division, Unit, and a Group.

[Department]-|---|<[Devision]-|---|<[Unit]-|---|<[Group]-|---|<[Employee]

😎

TJT SSCommitted Points: 1926 More actions · Answer 2

Yes this is straight forward, but what happens when a person doesn't have a group or a unit, but works just at the divisional level?

I am trying to imagine all possibilities.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

TJT (4/26/2014)
Yes this is straight forward, but what happens when a person doesn't have a group or a unit, but works just at the divisional level?
I am trying to imagine all possibilities.

I was kind of waiting for this question, this is a typical Ragged/Unbalanced hierarchy problem.

😎

This is one simple way of handling this:

[Department]-|--,

[Devision]-|---,|

[Unit]-|------,||

[Group]-|----,|||

''''--o<[Organizational Unit]-|---|<[Employee]

More info:

Inside Microsoft SQL Server 2008: T-SQL Querying (Developer Reference) by Itzik Ben-Gan

SQL Hierarchies – Parent Child[/url]

TJT SSCommitted Points: 1926 More actions · Answer 4

OK thanks, this is what I was thinking, but wanted confirmation on this.

Any other thoughts on this are appreciated

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

TJT (4/26/2014)
OK thanks, this is what I was thinking, but wanted confirmation on this.
Any other thoughts on this are appreciated

Kind of a "must read"

Joe Celko's Trees and Hierarchies in SQL for Smarties

😎