Re: Another newbie question

Chi wrote:
>
> If a company is setup in fthe following manner:
>
> COMPANY has many DIVISIONS which has many BRANCHES which has many
> UNITS
>
> and I have 4 tables to represent this:
>
> COMPANY
> -------
> COMPANY_ID (PK)
<SNIP>

> UNIT
> ----
> MINISTRY_ID (FK)
> DIVISION_ID (FK)
> BRANCH_ID (FK)
> UNIT_ID (PK)
> UNIT_NAME
I'm not an experienced Oracle person but I develop in a database (which I am afraid to name 8) ) so I think I can answer this one for you:

Presumeably you meant to write COMPANY_ID (FK) above where you wrote MINISTRY_ID (FK)? Even if you didn't, the following still has merit...

One thing to be considered is what happens to the Unit record when there's a reorganization in which, for example, a division moves from one company to another or a branch moves within the company to another division? You can do it, but you've got to have some mechanism whereby your cascaded FKs are changed...that leaves room for breaking things!

IMO, it's much more sensible to have an FK only for the level immediately above. The relations take care of any change mentioned above without having to write a routine to find all the unit records and edit appropriate company and branch and division FKs...

Hope this helps.

-- 
Tim - http://www.ucs.mun.ca/~tmarshal/
 ^o<  
 /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^  "DittoooOOO?" - Ditto