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?

On Tue, 24 May 2011 15:11:56 -0700 (PDT), stapes
<steve.staple (AT) gmail (DOT) com> wrote:

Are you sure about the cardinality of the data? Medical
information, for example, does not strike me as something that is
just-one-of (unless it is very abbreviated).

Sincerely,

Gene Wirchenko

stapes (steve.staple (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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