Posted by David Portas on 03/12/06 10:59

Erland Sommarskog wrote:
> David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> > You said "data" so you could be right. If you'd said "information"
> > you'd be wrong. Here's an analogy. If I have two copies of "Inside SQL
> > Server" on my bookshelf do I have more information than if I have one
> > copy of that book? Now if I write 1 on the cover of the first book and
> > 2 on the cover of the second, do I have any more information? So should
> > I spend money and storage space on two books or one? The smart money is
> > invested in information not in data.
>
> Then again, you could have scribbled notes in one of the copies, and
> the other could have Kalen's highly valuable autograph.

In that case they wouldn't be duplicates any more. To complete the
analogy, you now have a natural key. (Alternatively you might want to
decompose the notes and the signature into separate tables)

> More importantly, there is data - or information - out there that
> users want to - and need to - deal with, despite that we cannot define
> a unique key for them. You already know the prime example too well:
> customers.

Users are concerned with information. Data (how the information is
represented) is primarily the concern of database professionals. As a
database designer you have a choice because the same information can
always be modelled with natural keys or without. You can argue that the
developer may lack the time, the resources or the authority to redesign
his database. He may even be unable to analyse his business problem
well enough to identify a suitable key with a high degree of
confidence. However, those constraints are not problems we can solve in
a newsgroup. They are project management problems rather than technical
ones.

The technical solution to the OP's problem is simple: elminate
redundancy. It is always possible to eliminate redundancy as a
consequence of the simple fact that duplicate rows cannot contain more
information than a single row. That applies equally whether the row
represents customers or books or anything else. I suggest we let the OP
to decide if he has the will or the resources to implement the solution
in his case.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]