DB Design – Dynamic table columns issue! | SQL Server Performance Forums

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 />- &lt;Categories&gt;<br />- &lt;Registry&gt;<br />- &lt;RegFolderEnumeration&gt;<br />+ &lt;Communication&gt;<br /> &lt;Comm1&gt;80211b&lt;/Comm1&gt; <br /> &lt;Comm2&gt;AFD&lt;/Comm2&gt; <br /> &lt;Comm3&gt;ApplicationDownload&lt;/Comm3&gt; <br /> &lt;Comm4&gt;AsyncMac&lt;/Comm4&gt; <br /> &lt;Comm5&gt;AsyncMac1&lt;/Comm5&gt; <br /> &lt;Comm6&gt;Cxport&lt;/Comm6&gt; <br /> &lt;Comm7&gt;DefaultConnections&lt;/Comm7&gt; <br /> &lt;Comm8&gt;IrDA&lt;/Comm8&gt; <br /> &lt;Comm9&gt;Irsir&lt;/Comm9&gt; <br /> &lt;Comm10&gt;Irsir1&lt;/Comm10&gt; <br /> &lt;Comm11&gt;LAN9000&lt;/Comm11&gt; <br /> &lt;Comm12&gt;LAN90001&lt;/Comm12&gt; <br /> &lt;Comm13&gt;NE2000&lt;/Comm13&gt; <br /> &lt;Comm14&gt;NE20001&lt;/Comm14&gt; <br /> &lt;Comm15&gt;NETWLAN&lt;/Comm15&gt; <br /> &lt;Comm16&gt;NETWLAN1&lt;/Comm16&gt; <br /> &lt;Comm17&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />pp1&lt;/Comm17&gt; <br /> &lt;Comm18&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />PTP&lt;/Comm18&gt; <br /> &lt;Comm19&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />PTP1&lt;/Comm19&gt; <br /> &lt;Comm20&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />RISMNDS&lt;/Comm20&gt; <br /> &lt;Comm21&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />RISMNDS1&lt;/Comm21&gt; <br /> &lt;Comm22&gt;SAUSB&lt;/Comm22&gt; <br /> &lt;Comm23&gt;SAUSB1&lt;/Comm23&gt; <br /> &lt;Comm24&gt;Security&lt;/Comm24&gt; <br /> &lt;Comm25&gt;SecurityProviders&lt;/Comm25&gt; <br /> &lt;Comm26&gt;Tcpip&lt;/Comm26&gt; <br /> &lt;Comm27&gt;UDP2TCP&lt;/Comm27&gt; <br /> &lt;Comm28&gt;USB&lt;/Comm28&gt; <br /> &lt;/Communication&gt;<br />+ &lt;InstalledSoftwares&gt;<br /> &lt;Apps16&gt;Microsoft .NET CF 1.0 ENU-String Resource&lt;/Apps16&gt; <br /> &lt;Apps17&gt;Microsoft .NET Compact Framework&lt;/Apps17&gt; <br /> &lt;Apps18&gt;Microsoft Application Installer&lt;/Apps18&gt; <br /> &lt;Apps19&gt;My Company Stock_Replenishment&lt;/Apps19&gt; <br /> &lt;Apps20&gt;Shared&lt;/Apps20&gt; <br /> &lt;Apps21&gt;Soft Object Technologies Pocket Controller&lt;/Apps21&gt; <br /> &lt;Apps22&gt;SOTI&lt;/Apps22&gt; <br /> &lt;Apps23&gt;SOTI Pocket Controller&lt;/Apps23&gt; <br /> &lt;Apps29&gt;Tesco MSES Dummy Pocket Controller&lt;/Apps29&gt; <br /> &lt;Apps30&gt;Tesco MSES MSES Graphics Files 1&lt;/Apps30&gt; <br /> &lt;Apps31&gt;Tesco MSES MSES Graphics Files 2&lt;/Apps31&gt; <br /> &lt;Apps32&gt;Tesco MSES MSES Graphics Files 3&lt;/Apps32&gt; <br /> &lt;Apps33&gt;Tesco MSES MSES Graphics Files 4&lt;/Apps33&gt; <br /> &lt;/InstalledSoftwares&gt;<br /> &lt;/RegFolderEnumeration&gt;<br />- &lt;RegFolderContents&gt;<br />+ &lt;DotNetFramework&gt;<br /> &lt;CabFile&gt;SDMMC DiskCabFilesNETCF.CORE.PPC3.ARM.CAB&lt;/CabFile&gt; <br /> &lt;CmdFile&gt;WindowsAppMgrMicrosoft .NET Compact Framework.DAT&lt;/CmdFile&gt; <br /> &lt;InstallDir&gt;Program Files.NET Compact Framework&lt;/InstallDir&gt; <br /> &lt;Instl&gt;1&lt;/Instl&gt; <br /> &lt;InstlDir&gt;Program Files.NET Compact Framework&lt;/InstlDir&gt; <br /> &lt;InstlDirCnt&gt;1&lt;/InstlDirCnt&gt; <br /> &lt;IsvFile&gt;WindowsAppMgrMicrosoft .NET Compact Framework.DLL&lt;/IsvFile&gt; <br /> &lt;/DotNetFramework&gt;<br />+ &lt;TCPIP&gt;<br /> &lt;AutoInterval&gt;15&lt;/AutoInterval&gt; <br /> &lt;DefaultGateway&gt;64.26.0.16&lt;/DefaultGateway&gt; <br /> &lt;DNS&gt;64.26.0.1 193.5.0.9&lt;/DNS&gt; <br /> &lt;EnableDHCP&gt;0&lt;/EnableDHCP&gt; <br /> &lt;IpAddress&gt;64.26.3.115&lt;/IpAddress&gt; <br /> &lt;Subnetmask&gt;255.255.240.0&lt;/Subnetmask&gt; <br /> &lt;WINS /&gt; <br /> &lt;/TCPIP&gt;<br /> &lt;/RegFolderContents&gt;<br />- &lt;RegFile&gt;<br /> &lt;NTPTimeServer&gt;193.5.0.9&lt;/NTPTimeServer&gt; <br /> &lt;DNSServer&gt;64.26.0.1 193.5.0.9&lt;/DNSServer&gt; <br /> &lt;DefaultGateway&gt;64.26.0.16&lt;/DefaultGateway&gt; <br /> &lt;SubnetMask&gt;255.255.240.0&lt;/SubnetMask&gt; <br /> &lt;Timezone&gt;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&lt;/Timezone&gt; <br /> &lt;AutoSuspend&gt;180&lt;/AutoSuspend&gt; <br /> &lt;/RegFile&gt;<br /><br /><br />Thanks,<br />V Vidhya
You 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).
]]>