DB Design - Table layout

[aconybeare]

I have an oracle database, which is currently structured as follows -

LOBusiness (Parent - Top level) 1 To many with Division
  Division  1 To many with building, KBP etc...
    Building
    KBProcess
    Risk 
    ETC...

I have received a request to include subDivisions beneath divisions

LOBusiness (Parent - Top level) 1 To many with Division
  Division  0 or 1 To many with SubDivision (if  no subdivisions 1 To Many with Building, KBP etc...)
    SubDivision 1 To Many with Building, KBP etc...
      Building
      KBProcess
      Risk 
      ETC...

Simple enough, however some divisions don't have subDivisions
Does anyone have any advice, tips or tutorials on how to achieve this?

Hopefully this makes sense, however If not please let me know and I'll try and word it better.

Cheers,

[jim mcnamara]

Make subdivision a repeating table (1 to many) for division.
Keep the same table hierarchy you have.

You're over-normalizing and will get yourself into trouble.
Just add the fields you need to identify Division & subduivsion, ie. change the primary key to Division + subdivision (allow null subdivisions).

Keep the actual subdivision names, other stuff in a separate subdivision table for lookups. Add the same key to all of the child tables, use the same key for subdivisions.

Ie., some buildings will have several subdivisions and so on, some buildings will have no subdivisions, but you can reach all child records.

This will mean some more records in Buildings and so on.

[aconybeare]

Thanks Jim,

Sounds good, I will give it my best shot, may come back with one or two questions.

Many thanks for your help

Cheers