Proposal: First step towards Intelligent,integrated database - Mailing list pgsql-hackers

<font face="Arial"><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt"><font face="Calibri">Hello,</font><p
class="MsoNormal"style="MARGIN: 0cm 0cm 0pt"><font face="Calibri"></font> <p class="MsoNormal" style="MARGIN: 0cm 0cm
0pt"><fontface="Calibri">Here is the proposal: <span style="mso-spacerun: yes"> </span>My 1<sup>st</sup> step towards
Intelligent,Integrated database. I am explaining the proposal with the use of example. </font><p class="MsoNormal"
style="MARGIN:0cm 0cm 0pt"><font face="Calibri">Example: We will have a master table say CustMast and a transaction
tablesay salesOrder table.</font><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt"><font face="Calibri">View of
CustMast:</font><pclass="MsoNormal" style="MARGIN: 0cm 0cm 0pt 36pt"><font face="Calibri">CustCode<span
style="mso-spacerun:yes">    </span>Number(5),</font><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt 36pt"><font
face="Calibri">CustName<spanstyle="mso-spacerun: yes">  </span>Varchar(30),</font><p class="MsoNormal" style="MARGIN:
0cm0cm 0pt 36pt"><font face="Calibri">CustAdrsLine1<span style="mso-spacerun: yes">   </span>Varchar,</font><p
class="MsoNormal"style="MARGIN: 0cm 0cm 0pt 36pt"><font face="Calibri">CustAdrsLine2<span style="mso-spacerun: yes">  
</span>varchar<spanstyle="mso-spacerun: yes">  </span>etc.</font><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt"><font
face="Calibri">Viewof SalesOrder:</font><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt 36pt"><font
face="Calibri">Sordno<spanstyle="mso-spacerun: yes">    </span>Number(8),</font><p class="MsoNormal" style="MARGIN: 0cm
0cm0pt 36pt"><font face="Calibri">Sorddt<span style="mso-spacerun: yes">     </span>date,</font><p class="MsoNormal"
style="MARGIN:0cm 0cm 0pt 36pt"><font face="Calibri">CustCode<span style="mso-spacerun: yes">  </span>Number(5)<span
style="mso-spacerun:yes">  </span>-<span style="mso-spacerun: yes">  </span>present way of defining.</font><p
class="MsoNormal"style="MARGIN: 0cm 0cm 0pt"><font face="Calibri"><span style="mso-spacerun: yes"> </span>Proposed way
is:</font><pclass="MsoNormal" style="TEXT-INDENT: 36pt; MARGIN: 0cm 0cm 0pt"><b><font face="Calibri">CustCode Object
CustMast.<spanstyle="mso-spacerun: yes">     </span>--- New data type to be introduced called “O” Object and create
tabledefinition to be modified whenever data type is ‘O’, it will accept object name (in this case table name). Here I
wantto inform data definition that field and its data type is already defined in master table and use the same data
typehere and both tables are linked with this field.</font></b><p class="MsoNormal" style="MARGIN: 0cm 0cm
0pt"><b><fontface="Calibri">We will be using same field name in both tables if not along with table name field name is
tobe accepted in create table definition.</font></b><p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt"><font
face="Calibri">Advantages:</font><p class="MsoListParagraphCxSpFirst" style="TEXT-INDENT: -18pt; MARGIN: 0cm 0cm 0pt
36pt;mso-add-space: auto; mso-list: l0 level1 lfo1"><span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font:
minor-latin"><spanstyle="mso-list: Ignore"><font face="Calibri">1.</font><span style="FONT: 7pt 'Times New
Roman'">      </span></span></span><font face="Calibri">Now database knows that custcode in salesorder is a foreign
key,a table constraint can be created. It also knows that index to be created on this field.</font><p
class="MsoListParagraphCxSpMiddle"style="TEXT-INDENT: -18pt; MARGIN: 0cm 0cm 0pt 36pt; mso-add-space: auto; mso-list:
l0level1 lfo1"><span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin"><span style="mso-list:
Ignore"><fontface="Calibri">2.</font><span style="FONT: 7pt 'Times New Roman'">       </span></span></span><font
face="Calibri">Inpresent situation select statement for selecting values from join of both tables will be</font><p
class="MsoListParagraphCxSpMiddle"style="MARGIN: 0cm 0cm 0pt 36pt; mso-add-space: auto"><font face="Calibri">Select
sordno,sorddt, custcode, custname, custadrsline1 </font><p class="MsoListParagraphCxSpMiddle" style="MARGIN: 0cm 0cm
0pt36pt; mso-add-space: auto"><font face="Calibri">from salesorder, custmast </font><p
class="MsoListParagraphCxSpMiddle"style="MARGIN: 0cm 0cm 0pt 36pt; mso-add-space: auto"><font face="Calibri">where
salesorder.custcode=custmast.custcode.</font><pclass="MsoListParagraphCxSpMiddle" style="TEXT-INDENT: -18pt; MARGIN:
0cm0cm 0pt 36pt; mso-add-space: auto; mso-list: l0 level1 lfo1"><span style="mso-bidi-font-family: Calibri;
mso-bidi-theme-font:minor-latin"><span style="mso-list: Ignore"><font face="Calibri">3.</font><span style="FONT: 7pt
'TimesNew Roman'">       </span></span></span><font face="Calibri">In proposed way we can write this statement
as:</font><pclass="MsoListParagraphCxSpMiddle" style="MARGIN: 0cm 0cm 0pt 36pt; mso-add-space: auto"><font
face="Calibri">Selectsordno, sorddt, custcode, custname, custadrsline1 </font><p class="MsoListParagraphCxSpMiddle"
style="MARGIN:0cm 0cm 0pt 36pt; mso-add-space: auto"><font face="Calibri">from salesorder<span style="mso-spacerun:
yes"> </span>(with proper changes in program which pickup values from select statement.</font><p
class="MsoListParagraphCxSpMiddle"style="TEXT-INDENT: -18pt; MARGIN: 0cm 0cm 0pt 36pt; mso-add-space: auto; mso-list:
l0level1 lfo1"><span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin"><span style="mso-list:
Ignore"><fontface="Calibri">4.</font><span style="FONT: 7pt 'Times New Roman'">       </span></span></span><font
face="Calibri">Fieldcan be from another table in same database or from Excel sheet column.</font><p
class="MsoListParagraphCxSpMiddle"style="TEXT-INDENT: -18pt; MARGIN: 0cm 0cm 0pt 36pt; mso-add-space: auto; mso-list:
l0level1 lfo1"><span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin"><span style="mso-list:
Ignore"><fontface="Calibri">5.</font><span style="FONT: 7pt 'Times New Roman'">       </span></span></span><font
face="Calibri">Viewsneed not be created as all tables are properly linked with each other in an application. </font><p
class="MsoListParagraphCxSpMiddle"style="TEXT-INDENT: -18pt; MARGIN: 0cm 0cm 0pt 36pt; mso-add-space: auto; mso-list:
l0level1 lfo1"><span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin"><span style="mso-list:
Ignore"><fontface="Calibri">6.</font><span style="FONT: 7pt 'Times New Roman'">       </span></span></span><font
face="Calibri"><strong>Thisis only first step and many advantages can be a result of brainstorm.</strong> </font><p
class="MsoListParagraphCxSpMiddle"style="TEXT-INDENT: -18pt; MARGIN: 0cm 0cm 0pt 36pt; mso-add-space: auto; mso-list:
l0level1 lfo1"><font face="Calibri">7.    This will change RDBMS, Tools and ERP to next generation.</font><p
class="MsoListParagraphCxSpMiddle"style="TEXT-INDENT: -18pt; MARGIN: 0cm 0cm 0pt 36pt; mso-add-space: auto; mso-list:
l0level1 lfo1"><font face="Calibri"></font> <p class="MsoListParagraphCxSpMiddle" style="TEXT-INDENT: -18pt; MARGIN:
0cm0cm 0pt 36pt; mso-add-space: auto; mso-list: l0 level1 lfo1"><font face="Calibri">For any clarifications pl contact.
Plgive your feedback.</font><p class="MsoListParagraphCxSpLast" style="MARGIN: 0cm 0cm 0pt 36pt; mso-add-space:
auto"><spanstyle="mso-spacerun: yes"></span> <p class="MsoListParagraphCxSpLast" style="MARGIN: 0cm 0cm 0pt 36pt;
mso-add-space:auto"><span style="mso-spacerun: yes">Regards Many,</span><p class="MsoListParagraphCxSpLast"
style="MARGIN:0cm 0cm 0pt 36pt; mso-add-space: auto"><span style="mso-spacerun: yes">Vijay Ghatpande.</span><p
class="MsoListParagraphCxSpLast"style="MARGIN: 0cm 0cm 0pt 36pt; mso-add-space: auto"><span style="mso-spacerun:
yes">Cell:+91 9822456142.</span></font>