Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 2EAFE1337B39 for ; Wed, 1 Dec 2010 06:21:57 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 25410-03 for ; Wed, 1 Dec 2010 10:21:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from fe3.vsnl.net (fe3.vsnl.net [203.200.235.173]) by mail.postgresql.org (Postfix) with ESMTP id 55E16133740B for ; Wed, 1 Dec 2010 06:21:49 -0400 (AST) Received: from vsnl.net ([172.16.28.44]) by fe3.internal.vsnl.net (vsnl mail server fe3) with ESMTP id <0LCQ002I3VCKR990@fe3.internal.vsnl.net> for pgsql-hackers@postgresql.org; Wed, 01 Dec 2010 15:49:32 +0530 (IST) Received: from [172.16.28.177] (Forwarded-For: [117.195.43.4]) by store3.internal.vsnl.net (mshttpd); Wed, 01 Dec 2010 15:19:32 +0500 Date: Wed, 01 Dec 2010 15:19:32 +0500 From: ghatpande@vsnl.net Subject: Proposal: First step towards Intelligent,integrated database To: pgsql hackers Message-id: MIME-version: 1.0 X-Mailer: Sun Java(tm) System Messenger Express 6.2-6.01 (built Apr 3 2006) Content-type: multipart/alternative; boundary="Boundary_(ID_TC3B0k9EEZJbfM4s8UXJLA)" Content-language: en X-Accept-Language: en X-Priority: 1 (Highest) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.908 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201012/17 X-Sequence-Number: 175279 This is a multi-part message in MIME format. --Boundary_(ID_TC3B0k9EEZJbfM4s8UXJLA) Content-type: text/plain; charset=windows-1252 Content-transfer-encoding: quoted-printable Content-disposition: inline Hello=2C Here is the proposal=3A My 1st step towards Intelligent=2C Integrated d= atabase=2E I am explaining the proposal with the use of example=2E = Example=3A We will have a master table say CustMast and a transaction ta= ble say salesOrder table=2E View of CustMast=3A CustCode Number(5)=2C CustName Varchar(30)=2C CustAdrsLine1 Varchar=2C CustAdrsLine2 varchar etc=2E View of SalesOrder=3A Sordno Number(8)=2C Sorddt date=2C CustCode Number(5) - present way of defining=2E Proposed way is=3A CustCode Object CustMast=2E --- New data type to be introduced calle= d =93O=94 Object and create table definition to be modified whenever dat= a type is =91O=92=2C it will accept object name (in this case table name= )=2E Here I want to inform data definition that field and its data type = is already defined in master table and use the same data type here and b= oth tables are linked with this field=2E We will be using same field name in both tables if not along with table = name field name is to be accepted in create table definition=2E Advantages=3A = 1=2E Now database knows that custcode in salesorder is a foreign k= ey=2C a table constraint can be created=2E It also knows that index to b= e created on this field=2E 2=2E In present situation select statement for selecting values fr= om join of both tables will be Select sordno=2C sorddt=2C custcode=2C custname=2C custadrsline1 = from salesorder=2C custmast = where salesorder=2Ecustcode=3Dcustmast=2Ecustcode=2E 3=2E In proposed way we can write this statement as=3A Select sordno=2C sorddt=2C custcode=2C custname=2C custadrsline1 = from salesorder (with proper changes in program which pickup values fro= m select statement=2E 4=2E Field can be from another table in same database or from Exce= l sheet column=2E 5=2E Views need not be created as all tables are properly linked w= ith each other in an application=2E = 6=2E This is only first step and many advantages can be a result o= f brainstorm=2E = 7=2E This will change RDBMS=2C Tools and ERP to next generation=2E For any clarifications pl contact=2E Pl give your feedback=2E Regards Many=2C Vijay Ghatpande=2E Cell=3A +91 9822456142=2E --Boundary_(ID_TC3B0k9EEZJbfM4s8UXJLA) Content-type: text/html; charset=windows-1252 Content-transfer-encoding: quoted-printable Content-disposition: inline =3CBODY=3E=3CFONT face=3DArial=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt=22 class=3DMsoNormal=3E=3CFONT fac= e=3DCalibri=3EHello=2C=3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt=22 class=3DMsoNormal=3E=3CFONT fac= e=3DCalibri=3E=3C/FONT=3E=26nbsp=3B=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt=22 class=3DMsoNormal=3E=3CFONT fac= e=3DCalibri=3EHere is the proposal=3A =3CSPAN style=3D=22mso-spacerun=3A= yes=22=3E=26nbsp=3B=3C/SPAN=3EMy 1=3CSUP=3Est=3C/SUP=3E step towards In= telligent=2C Integrated database=2E I am explaining the proposal with th= e use of example=2E =3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt=22 class=3DMsoNormal=3E=3CFONT fac= e=3DCalibri=3EExample=3A We will have a master table say CustMast and a = transaction table say salesOrder table=2E=3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt=22 class=3DMsoNormal=3E=3CFONT fac= e=3DCalibri=3EView of CustMast=3A=3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=22 class=3DMsoNormal=3E=3CFON= T face=3DCalibri=3ECustCode=3CSPAN style=3D=22mso-spacerun=3A yes=22=3E=26= nbsp=3B=26nbsp=3B=26nbsp=3B =3C/SPAN=3ENumber(5)=2C=3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=22 class=3DMsoNormal=3E=3CFON= T face=3DCalibri=3ECustName=3CSPAN style=3D=22mso-spacerun=3A yes=22=3E=26= nbsp=3B =3C/SPAN=3EVarchar(30)=2C=3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=22 class=3DMsoNormal=3E=3CFON= T face=3DCalibri=3ECustAdrsLine1=3CSPAN style=3D=22mso-spacerun=3A yes=22= =3E=26nbsp=3B=26nbsp=3B =3C/SPAN=3EVarchar=2C=3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=22 class=3DMsoNormal=3E=3CFON= T face=3DCalibri=3ECustAdrsLine2=3CSPAN style=3D=22mso-spacerun=3A yes=22= =3E=26nbsp=3B=26nbsp=3B =3C/SPAN=3Evarchar=3CSPAN style=3D=22mso-spaceru= n=3A yes=22=3E=26nbsp=3B =3C/SPAN=3Eetc=2E=3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt=22 class=3DMsoNormal=3E=3CFONT fac= e=3DCalibri=3EView of SalesOrder=3A=3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=22 class=3DMsoNormal=3E=3CFON= T face=3DCalibri=3ESordno=3CSPAN style=3D=22mso-spacerun=3A yes=22=3E=26= nbsp=3B=26nbsp=3B=26nbsp=3B =3C/SPAN=3ENumber(8)=2C=3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=22 class=3DMsoNormal=3E=3CFON= T face=3DCalibri=3ESorddt=3CSPAN style=3D=22mso-spacerun=3A yes=22=3E=26= nbsp=3B=26nbsp=3B=26nbsp=3B=26nbsp=3B =3C/SPAN=3Edate=2C=3C/FONT=3E=3C/P= =3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=22 class=3DMsoNormal=3E=3CFON= T face=3DCalibri=3ECustCode=3CSPAN style=3D=22mso-spacerun=3A yes=22=3E=26= nbsp=3B =3C/SPAN=3ENumber(5)=3CSPAN style=3D=22mso-spacerun=3A yes=22=3E= =26nbsp=3B =3C/SPAN=3E-=3CSPAN style=3D=22mso-spacerun=3A yes=22=3E=26nb= sp=3B =3C/SPAN=3Epresent way of defining=2E=3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt=22 class=3DMsoNormal=3E=3CFONT fac= e=3DCalibri=3E=3CSPAN style=3D=22mso-spacerun=3A yes=22=3E=26nbsp=3B=3C/= SPAN=3EProposed way is=3A=3C/FONT=3E=3C/P=3E =3CP style=3D=22TEXT-INDENT=3A 36pt=3B MARGIN=3A 0cm 0cm 0pt=22 class=3D= MsoNormal=3E=3CB=3E=3CFONT face=3DCalibri=3ECustCode Object CustMast=2E=3C= SPAN style=3D=22mso-spacerun=3A yes=22=3E=26nbsp=3B=26nbsp=3B=26nbsp=3B=26= nbsp=3B =3C/SPAN=3E--- New data type to be introduced called =93O=94 Obj= ect and create table definition to be modified whenever data type is =91= O=92=2C it will accept object name (in this case table name)=2E Here I w= ant to inform data definition that field and its data type is already de= fined in master table and use the same data type here and both tables ar= e linked with this field=2E=3C/FONT=3E=3C/B=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt=22 class=3DMsoNormal=3E=3CB=3E=3CF= ONT face=3DCalibri=3EWe will be using same field name in both tables if = not along with table name field name is to be accepted in create table d= efinition=2E=3C=3Fxml=3Anamespace prefix =3D o ns =3D =22urn=3Aschemas-m= icrosoft-com=3Aoffice=3Aoffice=22 /=3E=3Co=3Ap=3E=3C/o=3Ap=3E=3C/FONT=3E= =3C/B=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt=22 class=3DMsoNormal=3E=3CFONT fac= e=3DCalibri=3EAdvantages=3A =3C/FONT=3E=3C/P=3E =3CP style=3D=22TEXT-INDENT=3A -18pt=3B MARGIN=3A 0cm 0cm 0pt 36pt=3B ms= o-add-space=3A auto=3B mso-list=3A l0 level1 lfo1=22 class=3DMsoListPara= graphCxSpFirst=3E=3CSPAN style=3D=22mso-bidi-font-family=3A Calibri=3B m= so-bidi-theme-font=3A minor-latin=22=3E=3CSPAN style=3D=22mso-list=3A Ig= nore=22=3E=3CFONT face=3DCalibri=3E1=2E=3C/FONT=3E=3CSPAN style=3D=22FON= T=3A 7pt =27Times New Roman=27=22=3E=26nbsp=3B=26nbsp=3B=26nbsp=3B=26nbs= p=3B=26nbsp=3B=26nbsp=3B =3C/SPAN=3E=3C/SPAN=3E=3C/SPAN=3E=3CFONT face=3D= Calibri=3ENow database knows that custcode in salesorder is a foreign ke= y=2C a table constraint can be created=2E It also knows that index to be= created on this field=2E=3C/FONT=3E=3C/P=3E =3CP style=3D=22TEXT-INDENT=3A -18pt=3B MARGIN=3A 0cm 0cm 0pt 36pt=3B ms= o-add-space=3A auto=3B mso-list=3A l0 level1 lfo1=22 class=3DMsoListPara= graphCxSpMiddle=3E=3CSPAN style=3D=22mso-bidi-font-family=3A Calibri=3B = mso-bidi-theme-font=3A minor-latin=22=3E=3CSPAN style=3D=22mso-list=3A I= gnore=22=3E=3CFONT face=3DCalibri=3E2=2E=3C/FONT=3E=3CSPAN style=3D=22FO= NT=3A 7pt =27Times New Roman=27=22=3E=26nbsp=3B=26nbsp=3B=26nbsp=3B=26nb= sp=3B=26nbsp=3B=26nbsp=3B =3C/SPAN=3E=3C/SPAN=3E=3C/SPAN=3E=3CFONT face=3D= Calibri=3EIn present situation select statement for selecting values fro= m join of both tables will be=3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=3B mso-add-space=3A auto=22 c= lass=3DMsoListParagraphCxSpMiddle=3E=3CFONT face=3DCalibri=3ESelect sord= no=2C sorddt=2C custcode=2C custname=2C custadrsline1 =3C/FONT=3E=3C/P=3E= =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=3B mso-add-space=3A auto=22 c= lass=3DMsoListParagraphCxSpMiddle=3E=3CFONT face=3DCalibri=3Efrom saleso= rder=2C custmast =3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=3B mso-add-space=3A auto=22 c= lass=3DMsoListParagraphCxSpMiddle=3E=3CFONT face=3DCalibri=3Ewhere sales= order=2Ecustcode=3Dcustmast=2Ecustcode=2E=3C/FONT=3E=3C/P=3E =3CP style=3D=22TEXT-INDENT=3A -18pt=3B MARGIN=3A 0cm 0cm 0pt 36pt=3B ms= o-add-space=3A auto=3B mso-list=3A l0 level1 lfo1=22 class=3DMsoListPara= graphCxSpMiddle=3E=3CSPAN style=3D=22mso-bidi-font-family=3A Calibri=3B = mso-bidi-theme-font=3A minor-latin=22=3E=3CSPAN style=3D=22mso-list=3A I= gnore=22=3E=3CFONT face=3DCalibri=3E3=2E=3C/FONT=3E=3CSPAN style=3D=22FO= NT=3A 7pt =27Times New Roman=27=22=3E=26nbsp=3B=26nbsp=3B=26nbsp=3B=26nb= sp=3B=26nbsp=3B=26nbsp=3B =3C/SPAN=3E=3C/SPAN=3E=3C/SPAN=3E=3CFONT face=3D= Calibri=3EIn proposed way we can write this statement as=3A=3C/FONT=3E=3C= /P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=3B mso-add-space=3A auto=22 c= lass=3DMsoListParagraphCxSpMiddle=3E=3CFONT face=3DCalibri=3ESelect sord= no=2C sorddt=2C custcode=2C custname=2C custadrsline1 =3C/FONT=3E=3C/P=3E= =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=3B mso-add-space=3A auto=22 c= lass=3DMsoListParagraphCxSpMiddle=3E=3CFONT face=3DCalibri=3Efrom saleso= rder=3CSPAN style=3D=22mso-spacerun=3A yes=22=3E=26nbsp=3B =3C/SPAN=3E(w= ith proper changes in program which pickup values from select statement=2E= =3C/FONT=3E=3C/P=3E =3CP style=3D=22TEXT-INDENT=3A -18pt=3B MARGIN=3A 0cm 0cm 0pt 36pt=3B ms= o-add-space=3A auto=3B mso-list=3A l0 level1 lfo1=22 class=3DMsoListPara= graphCxSpMiddle=3E=3CSPAN style=3D=22mso-bidi-font-family=3A Calibri=3B = mso-bidi-theme-font=3A minor-latin=22=3E=3CSPAN style=3D=22mso-list=3A I= gnore=22=3E=3CFONT face=3DCalibri=3E4=2E=3C/FONT=3E=3CSPAN style=3D=22FO= NT=3A 7pt =27Times New Roman=27=22=3E=26nbsp=3B=26nbsp=3B=26nbsp=3B=26nb= sp=3B=26nbsp=3B=26nbsp=3B =3C/SPAN=3E=3C/SPAN=3E=3C/SPAN=3E=3CFONT face=3D= Calibri=3EField can be from another table in same database or from Excel= sheet column=2E=3C/FONT=3E=3C/P=3E =3CP style=3D=22TEXT-INDENT=3A -18pt=3B MARGIN=3A 0cm 0cm 0pt 36pt=3B ms= o-add-space=3A auto=3B mso-list=3A l0 level1 lfo1=22 class=3DMsoListPara= graphCxSpMiddle=3E=3CSPAN style=3D=22mso-bidi-font-family=3A Calibri=3B = mso-bidi-theme-font=3A minor-latin=22=3E=3CSPAN style=3D=22mso-list=3A I= gnore=22=3E=3CFONT face=3DCalibri=3E5=2E=3C/FONT=3E=3CSPAN style=3D=22FO= NT=3A 7pt =27Times New Roman=27=22=3E=26nbsp=3B=26nbsp=3B=26nbsp=3B=26nb= sp=3B=26nbsp=3B=26nbsp=3B =3C/SPAN=3E=3C/SPAN=3E=3C/SPAN=3E=3CFONT face=3D= Calibri=3EViews need not be created as all tables are properly linked wi= th each other in an application=2E =3C/FONT=3E=3C/P=3E =3CP style=3D=22TEXT-INDENT=3A -18pt=3B MARGIN=3A 0cm 0cm 0pt 36pt=3B ms= o-add-space=3A auto=3B mso-list=3A l0 level1 lfo1=22 class=3DMsoListPara= graphCxSpMiddle=3E=3CSPAN style=3D=22mso-bidi-font-family=3A Calibri=3B = mso-bidi-theme-font=3A minor-latin=22=3E=3CSPAN style=3D=22mso-list=3A I= gnore=22=3E=3CFONT face=3DCalibri=3E6=2E=3C/FONT=3E=3CSPAN style=3D=22FO= NT=3A 7pt =27Times New Roman=27=22=3E=26nbsp=3B=26nbsp=3B=26nbsp=3B=26nb= sp=3B=26nbsp=3B=26nbsp=3B =3C/SPAN=3E=3C/SPAN=3E=3C/SPAN=3E=3CFONT face=3D= Calibri=3E=3CSTRONG=3EThis is only first step and many advantages can be= a result of brainstorm=2E=3C/STRONG=3E =3C/FONT=3E=3C/P=3E =3CP style=3D=22TEXT-INDENT=3A -18pt=3B MARGIN=3A 0cm 0cm 0pt 36pt=3B ms= o-add-space=3A auto=3B mso-list=3A l0 level1 lfo1=22 class=3DMsoListPara= graphCxSpMiddle=3E=3CFONT face=3DCalibri=3E7=2E=26nbsp=3B=26nbsp=3B=26nb= sp=3B This will change RDBMS=2C Tools and ERP to next generation=2E=3C/F= ONT=3E=3C/P=3E =3CP style=3D=22TEXT-INDENT=3A -18pt=3B MARGIN=3A 0cm 0cm 0pt 36pt=3B ms= o-add-space=3A auto=3B mso-list=3A l0 level1 lfo1=22 class=3DMsoListPara= graphCxSpMiddle=3E=3CFONT face=3DCalibri=3E=3C/FONT=3E=26nbsp=3B=3C/P=3E= =3CP style=3D=22TEXT-INDENT=3A -18pt=3B MARGIN=3A 0cm 0cm 0pt 36pt=3B ms= o-add-space=3A auto=3B mso-list=3A l0 level1 lfo1=22 class=3DMsoListPara= graphCxSpMiddle=3E=3CFONT face=3DCalibri=3EFor any clarifications pl con= tact=2E Pl give your feedback=2E=3C/FONT=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=3B mso-add-space=3A auto=22 c= lass=3DMsoListParagraphCxSpLast=3E=3CSPAN style=3D=22mso-spacerun=3A yes= =22=3E=3C/SPAN=3E=26nbsp=3B=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=3B mso-add-space=3A auto=22 c= lass=3DMsoListParagraphCxSpLast=3E=3CSPAN style=3D=22mso-spacerun=3A yes= =22=3ERegards Many=2C=3C/SPAN=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=3B mso-add-space=3A auto=22 c= lass=3DMsoListParagraphCxSpLast=3E=3CSPAN style=3D=22mso-spacerun=3A yes= =22=3EVijay Ghatpande=2E=3C/SPAN=3E=3C/P=3E =3CP style=3D=22MARGIN=3A 0cm 0cm 0pt 36pt=3B mso-add-space=3A auto=22 c= lass=3DMsoListParagraphCxSpLast=3E=3CSPAN style=3D=22mso-spacerun=3A yes= =22=3ECell=3A +91 9822456142=2E=3C/SPAN=3E=3C/P=3E=3C/FONT=3E=3C/BODY=3E --Boundary_(ID_TC3B0k9EEZJbfM4s8UXJLA)--