Re: Design table with many columns

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
>
>

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