Re: SQL: flattening a hierarchy
Date: 1995/06/04
Message-ID: <3qt4i8$vha_at_yell.zetnet.co.uk>#1/1
> While this is obviously play data, Im doing something like it for
> an application, and find it to be a real bitch.
> The input file lends itself to the Oracle connect by/start sql extension,
> which allows one to walk the file hierarchically. However,
> I need to rearraign the file to a seperate physical table to
> support listbox's and the like, and a flattened structure will be
> much more useful.
> The input is:
> Parent Child Level
> ------ ----- -----
> AllCars 1
> AllCars Ford 2
> Ford FullSize 3
> Ford Sports 3
> Sports Mustang 4
> Sports Probe 4
> Ford Utility 3
> FullSize Taurus 4
> Ford Vans 3
> Vans MiniVan 4
> AllCars GM 2
> I want to flatten it to something like:
> Level1 Level2 Level3
> ------ -------- -------
> Ford FullSize Taurus
> Ford Vans MiniVan
> Ford Utility Explorer
> Ford Sports Mustang
> Ford Sports Probe
> GM ................
> Thx, Mark
> --
> Mark Aurit
> Finance Client/Server Systems
> Northrop Grumman Data Systems (West)
> maurit_at_world.nad.northrop.com
If you look at the entities involved they seem to be MAKER, TYPE OF VEHICLE, MODEL
Where
Each MODEL must be of one and only one TYPE OF VEHICLE Each TYPE OF VEHICLE must be made by one and only one MAKER
The entities are therefore at 3 levels.
If you consider the UID of the bottom level then it must contain the UIDs of all 3 levels, since the UID of the 2nd level includes the relationship to the top entity, and so on.
As far as I can tell you simply want to denormalise the three entities into one flat file. This is perfectly legal but carries the risk of inconsistancies. eg You could have a record "Fard Vans Minivans" You therefore have to validate the data more than if you used conventional third normal form. If you are importing data from a system that guarentees its accuracy then you have no problem.
Hope this helps
Dennis Received on Sun Jun 04 1995 - 00:00:00 CEST