Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: What is the advantage to this kind of database structure? Date: Wed, 25 May 2011 23:32:55 +0200 Organization: Erland Sommarskog Lines: 31 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx04.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="16656"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+2/rAPSqBc/lqp/VVBuV5V" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:F5dyoFbi7RlGHbQGvaOUb6nanv0= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:376 stapes (steve.staple@gmail.com) writes: > I have had to do maintenance work on a number of databases that have > been created using what seems to me a very complex structure. > > For instance, instead of having one table of information on members of > staff, there are 20. Separate tables exist for items which are > compulsory - such as Medical information, Criminal Records, etc. I can > understand this in the case of optional items, which may or may not > exist, such as Interview results, or items which may exist in > multiples, such as References. > > Is there any advantage to this kind of structure? It's difficult to tell without seeing the full picture. But as Gene points out, cardinality is a big deal. If there is one row for each offense in the CriminalRecords table, you can't have that data in the main table. But from what you say "CriminalRecord" is a single column, presumably a blob column. Maybe someone thought it would be better to have them separated for some not such a good reason, for instance because they use SELECT * all over the place. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx