Re: Why Varying Arrasy and Nested Tables?
Date: 14 Oct 2002 13:09:08 -0700
Message-ID: <548b9514.0210141209.63cab34b_at_posting.google.com>
"Bryan & Sarah Baker" <bakers65_at_cox.net> wrote in message news:<tLAq9.42271$XF.24866_at_news1.central.cox.net>...
> We use nested tables in our database design and it has made things a lot
> easier for us since we have done theis.
>
> Say you have data that you collect over time for comparison or computation.
>
> Table = time_series_values (columns = date_time, value, location_code)
>
> Now lets say you sample Dissolved Oxygen and temperature in a lake at a
> specific point (Lat Long = location_code). In your sampling, you do those
> two measurements at different depths. After you are done you have
>
> Depth, DO, Temp
> 1, 2, 60
> 2, 2.5,59
> etc
>
>
> Well if you have value in the time_series_values tables setup as a nested
> table of (depth, Dissolved Oxygen, temp), you have have a more efficient
> normalized table then if your time_series table was (date_time, depth, DO,
> Temp, location code = table without nested tables) Why? Well, if your
> sampling had 20 measurements you would have 1 row with the nested table or
> 20 rows with out nested tables.
>
> We like nested tables over nested arrays since you set your array size and
> you can exceed that without recreating the table. Nested tables are
> variable in length. You can have as many or as few as you want/need.
>
> Bryan Baker
>
All that is fine, but my main point against the use of nested tables
is that data reliability is not guaranteed. Considering the sample
data
Depth, DO, Temp
1, 2, 60 2, 2.5, 59
etc
The database cannot prevent another row that has duplicate data from being added.
Depth, DO, Temp
1, 3, 60
A simple programming error by an inexperienced developer can compromise the reliability of the entire database!! Think about how complicated it is to even find out if something like that has happened. The traditional 3NF database design will prevent that from occurring.
But, if you dont care that for a particular location, date, depth and temperature, you have two different Dissolved Oxygen readings, then you are fine. If you do care, then you are setting yourself up to a lot of grief by using nested tables.
As far as storing the data in 1 row or in 100 rows, it will make very little difference in retrival if proper indexes are used.
- rajXesh