Content-Type: multipart/alternative;
 boundary="------------XWs0J1ZBxbkuEDD3t4Vbm5OX"
Message-ID: <dfc7a258-ef40-46fe-9ecd-348b30262e8f@gmail.com>
Date: Sun, 7 Jul 2024 10:54:19 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Design table with many columns
To: rjgoulet@comcast.net, Oracle L <oracle-l@freelists.org>
References: <6f3d3831-4583-4cba-b652-f34039c97c77@comcast.net>
Content-Language: en-US
From: Tim Gorman <tim.evdbt@gmail.com>
Organization: Bridgeton Data, Inc.
In-Reply-To: <6f3d3831-4583-4cba-b652-f34039c97c77@comcast.net>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: tim.evdbt@gmail.com
--------------XWs0J1ZBxbkuEDD3t4Vbm5OX
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Richard,

What you're describing is very similar to the concept of satellite 
tables in Data Vault, if I'm not mistaken.

The Oracle PIVOT and UNPIVOT operations can be used to convert rows into 
columns or back from columns into rows, allowing the storage advantages 
of the row-based satellite table concept, as well as the advantages of 
presenting a wide view with lots of columns during fetch.

The main question then become whether to make the presentation layer a 
view or a materialized view, depending on how frequently the 
presentation layer is used and how often the storage layer is loaded.  
Obviously, if the data is retrieved constantly and quickly but is 
modified relatively infrequently, then a materialized view has 
advantages.  Otherwise, a view may be sufficient, especially if the data 
changes more rapidly than it is queried.  Luckily, in most RDBMS's it is 
relatively easy to switch between views and materialized views, to 
determine best fit.

It might be helpful to brush up on Data Vault, perhaps starting with 
this Wikipedia article (HERE 
<https://en.wikipedia.org/wiki/Data_vault_modeling>)?

Hope this helps?

-Tim


On 7/7/2024 8:37 AM, richard goulet wrote:
> Not sure what happen, but the digest from yesterday cut up the message 
> pretty well.
>
> This is a pretty old issue that has been around for many a decade, 
> even before DBMS's appeared.  The solution from back then is to assign 
> each transaction some type of ID that can then have a header table as 
> well as a multi row spec table where each distinct and variable 
> attribute can be stored without making a mess of the RDBMS.  Let me go 
> back to a VERY OLD configuration I worked on and with:
>
>     We had a header table with a NSN, NOUN, and other associated 
> columns and a SPECS table that has the associated NSN column, a 
> SPEC_NAME and a VALUE columns as well as others that held the variable 
> data.
>
>     Some NSN's had 10 specs rows while some has several hundred 
> depending on what you were describing.  The NSN was the primary key in 
> the first table and a foreign key in the second.
>
> Did something similar in civilian life afterwards for electronic 
> modules in a manufacturing environment and that ported nicely between 
> databases as well so we could have Oracle internally and Postgres on 
> the external web site.  Same queries worked on both sides.
>
> -- 
> http://www.freelists.org/webpage/oracle-l
>
>
--------------XWs0J1ZBxbkuEDD3t4Vbm5OX
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Richard,</p>
    <p>What you're describing is very similar to the concept of
      satellite tables in Data Vault, if I'm not mistaken.</p>
    <p>The Oracle PIVOT and UNPIVOT operations can be used to convert
      rows into columns or back from columns into rows, allowing the
      storage advantages of the row-based satellite table concept, as
      well as the advantages of presenting a wide view with lots of
      columns during fetch.</p>
    <p>The main question then become whether to make the presentation
      layer a view or a materialized view, depending on how frequently
      the presentation layer is used and how often the storage layer is
      loaded.  Obviously, if the data is retrieved constantly and
      quickly but is modified relatively infrequently, then a
      materialized view has advantages.  Otherwise, a view may be
      sufficient, especially if the data changes more rapidly than it is
      queried.  Luckily, in most RDBMS's it is relatively easy to switch
      between views and materialized views, to determine best fit.</p>
    <p>It might be helpful to brush up on Data Vault, perhaps starting
      with this Wikipedia article (<a
        href="https://en.wikipedia.org/wiki/Data_vault_modeling">HERE</a>)?<br>
    </p>
    <p>Hope this helps?</p>
    <p>-Tim<br>
    </p>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 7/7/2024 8:37 AM, richard goulet
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:6f3d3831-4583-4cba-b652-f34039c97c77@comcast.net">Not
      sure what happen, but the digest from yesterday cut up the message
      pretty well.
      <br>
      <br>
      This is a pretty old issue that has been around for many a decade,
      even before DBMS's appeared.  The solution from back then is to
      assign each transaction some type of ID that can then have a
      header table as well as a multi row spec table where each distinct
      and variable attribute can be stored without making a mess of the
      RDBMS.  Let me go back to a VERY OLD configuration I worked on and
      with:
      <br>
      <br>
          We had a header table with a NSN, NOUN, and other associated
      columns and a SPECS table that has the associated NSN column, a
      SPEC_NAME and a VALUE columns as well as others that held the
      variable data.
      <br>
      <br>
          Some NSN's had 10 specs rows while some has several hundred
      depending on what you were describing.  The NSN was the primary
      key in the first table and a foreign key in the second.
      <br>
      <br>
      Did something similar in civilian life afterwards for electronic
      modules in a manufacturing environment and that ported nicely
      between databases as well so we could have Oracle internally and
      Postgres on the external web site.  Same queries worked on both
      sides.
      <br>
      <br>
      --
      <br>
      <a class="moz-txt-link-freetext" href="http://www.freelists.org/webpage/oracle-l">http://www.freelists.org/webpage/oracle-l</a>
      <br>
      <br>
      <br>
    </blockquote>
  </body>
</html>

--------------XWs0J1ZBxbkuEDD3t4Vbm5OX--
--
http://www.freelists.org/webpage/oracle-l