SQL Server Performance Forum – Threads Archive
DB Design – Dynamic table columns issue!
Hi all,<br /><br />I’m having following DB design at hand. <br />I need to design a table to store the details from a xml file.<br />I’ve reproduced a partial output from an XML file<br />I would like to<font color="blue"></font id="blue"> draw your attention to the tag ‘Registry’.<br /><br />As one can see, ‘Registry’ contains 3 sub-sections, ‘RegFolderEnumeration’,’RegFolderContents’,’RegFile’.<br />I need to design a table to store the values under each sub-section.<br /><br />Now, the problem is as follows:<br />The entries under each sub-section is dynamic. i.e. there can be any number of entries under each sub-section. Also, the contents/tag-name is dynamic in nature.<br /><br />How should I design a table for storing such dynamic contents?<br /><br />Any help is greatly appreciated. Please feel free to point me to any source of help in this regard. <br /><br />Thanks.<br /><br />XML FILE<br />———<br />- <Categories><br />- <Registry><br />- <RegFolderEnumeration><br />+ <Communication><br /> <Comm1>80211b</Comm1> <br /> <Comm2>AFD</Comm2> <br /> <Comm3>ApplicationDownload</Comm3> <br /> <Comm4>AsyncMac</Comm4> <br /> <Comm5>AsyncMac1</Comm5> <br /> <Comm6>Cxport</Comm6> <br /> <Comm7>DefaultConnections</Comm7> <br /> <Comm8>IrDA</Comm8> <br /> <Comm9>Irsir</Comm9> <br /> <Comm10>Irsir1</Comm10> <br /> <Comm11>LAN9000</Comm11> <br /> <Comm12>LAN90001</Comm12> <br /> <Comm13>NE2000</Comm13> <br /> <Comm14>NE20001</Comm14> <br /> <Comm15>NETWLAN</Comm15> <br /> <Comm16>NETWLAN1</Comm16> <br /> <Comm17><img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />pp1</Comm17> <br /> <Comm18><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />PTP</Comm18> <br /> <Comm19><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />PTP1</Comm19> <br /> <Comm20><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />RISMNDS</Comm20> <br /> <Comm21><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />RISMNDS1</Comm21> <br /> <Comm22>SAUSB</Comm22> <br /> <Comm23>SAUSB1</Comm23> <br /> <Comm24>Security</Comm24> <br /> <Comm25>SecurityProviders</Comm25> <br /> <Comm26>Tcpip</Comm26> <br /> <Comm27>UDP2TCP</Comm27> <br /> <Comm28>USB</Comm28> <br /> </Communication><br />+ <InstalledSoftwares><br /> <Apps16>Microsoft .NET CF 1.0 ENU-String Resource</Apps16> <br /> <Apps17>Microsoft .NET Compact Framework</Apps17> <br /> <Apps18>Microsoft Application Installer</Apps18> <br /> <Apps19>My Company Stock_Replenishment</Apps19> <br /> <Apps20>Shared</Apps20> <br /> <Apps21>Soft Object Technologies Pocket Controller</Apps21> <br /> <Apps22>SOTI</Apps22> <br /> <Apps23>SOTI Pocket Controller</Apps23> <br /> <Apps29>Tesco MSES Dummy Pocket Controller</Apps29> <br /> <Apps30>Tesco MSES MSES Graphics Files 1</Apps30> <br /> <Apps31>Tesco MSES MSES Graphics Files 2</Apps31> <br /> <Apps32>Tesco MSES MSES Graphics Files 3</Apps32> <br /> <Apps33>Tesco MSES MSES Graphics Files 4</Apps33> <br /> </InstalledSoftwares><br /> </RegFolderEnumeration><br />- <RegFolderContents><br />+ <DotNetFramework><br /> <CabFile>SDMMC DiskCabFilesNETCF.CORE.PPC3.ARM.CAB</CabFile> <br /> <CmdFile>WindowsAppMgrMicrosoft .NET Compact Framework.DAT</CmdFile> <br /> <InstallDir>Program Files.NET Compact Framework</InstallDir> <br /> <Instl>1</Instl> <br /> <InstlDir>Program Files.NET Compact Framework</InstlDir> <br /> <InstlDirCnt>1</InstlDirCnt> <br /> <IsvFile>WindowsAppMgrMicrosoft .NET Compact Framework.DLL</IsvFile> <br /> </DotNetFramework><br />+ <TCPIP><br /> <AutoInterval>15</AutoInterval> <br /> <DefaultGateway>64.26.0.16</DefaultGateway> <br /> <DNS>64.26.0.1 193.5.0.9</DNS> <br /> <EnableDHCP>0</EnableDHCP> <br /> <IpAddress>64.26.3.115</IpAddress> <br /> <Subnetmask>255.255.240.0</Subnetmask> <br /> <WINS /> <br /> </TCPIP><br /> </RegFolderContents><br />- <RegFile><br /> <NTPTimeServer>193.5.0.9</NTPTimeServer> <br /> <DNSServer>64.26.0.1 193.5.0.9</DNSServer> <br /> <DefaultGateway>64.26.0.16</DefaultGateway> <br /> <SubnetMask>255.255.240.0</SubnetMask> <br /> <Timezone>182 254 255 255 77 0 84 0 43 0 53 0 58 0 51 0 48 0 32 0 78 0 101 0 119 0 32 0 68 0 101 0 108 0 104 0 105 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 84 0 43 0 53 0 58 0 51 0 48 0 32 0 78 0 101 0 119 0 32 0 68 0 101 0 108 0 104 0 105 0 34 0 73 0 110 0 100 0 105 0 97 0 32 0 83 0 116 0 97 0 110 0 100 0 97 0 114 0 100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 196 255 255 255</Timezone> <br /> <AutoSuspend>180</AutoSuspend> <br /> </RegFile><br /><br /><br />Thanks,<br />V VidhyaYou need to normalize your table design. If each of the sections can have multiple entries, you need to create a master table that represents the Registry, and then a separate detail table for each of the registry sections, each with a foreign key column that refers to the Registry table. EDIT: Think vertical (rows), not horizontal (columns).
]]>