Self relation Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Self relation Table

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-07-29 : 22:39:34

I want to verify if this is good design. Here's the table setup:

OrganizationalType
--------------------
OrganizationalTypeID
Name

OrganizationalUnit
--------------------
OrganizationalUnitID
OrganizationalLevelID
ParentOrganizationalUnitID

Example Data:

OrganizationalType
-------------------
OrganizationalTypeID Name
1 Corporate (root)
2 Division
3 Region

OrganizationalUnit
---------------------------------------
OrganizationalUnitID OrganizationalTypeID ParentOrgUnitID Name
1 2 1 AAG
2 3 2 Central
3 2 1 DDR
4 3 2 Eastern

So essentially, the OrganizationalUnit table is acting as a table to hold all those types of data and their specific name values. So I could enter region names and division names into the OrganizationalUnit.

To me, this should be separated out (normalized) so that I have specific tables such as Division and Region. But maybe this is an efficient design. I just see a lot of intermixed types of data in one table and I guess I have never seen this before. I am also looking at this from a usability perspective as a programmer wondering how using these two tables is going to bubble up into my Data Layer in my OOP code.

Let me know your thoughts. Is this standard but more importantly good table design for a situation like this where you are storing organizational level information like this?
3

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-07-30 : 03:47:42

It's a pretty standard way of doing it. A couple of points:

a) The level is usually derrived from the hierachy not stored
b) NULL is traditionally used for the top level where there is no parent.

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-07-30 : 11:04:02

Thanks.

>>The level is usually derived from the hierachy not stored

I guess I don't see how you'd relate the unit to its type if you don't have OrganizationalTypeID in the OrganizationalUnit table. Is that what you mean?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-07-30 : 22:17:51

Ok. I missed the point that level was actually typeID, so you're correct.
When I read it I thought it was representing the depth down the tree.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources