Message-ID: <6f3d3831-4583-4cba-b652-f34039c97c77@comcast.net>
Date: Sun, 7 Jul 2024 11:37:30 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
To: Oracle L <oracle-l@freelists.org>
From: richard goulet <rjgoulet@comcast.net>
Subject: Design table with many columns
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: rjgoulet@comcast.net

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