[thelist] Simple table question...

[Ken Schaefer]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Jonathan Dillon
: Subject: RE: [thelist] Simple table question...
: 
: <snippage>
: > You need to answer the following question. Given the following data,
: > what should be returned by your query?
: >
: > joe at blow.com Joe Blow 555-555-5555
: > joe at blow.com Joe Blow 555-555-5556
: 
: This actually finally clicked.
: 
: Look, in relation to the question of proper architecture, this is 
: a really bad table.  BAD table no donut.  Didn't architect, 
: can't change, as a lot of recurring transactions come off 
: this table.
: 
: Soooo... I'm screwed.  I've never worked on a db with this kind of flat
: architecture before (which is probably why I've never seen this).  It's
: retarded, like I said. ;-)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I don't think there's anything wrong with the table structure per se (given
what you've told us). What you probably needed was better constraints on
entering data in the first place (e.g. a unique constraint on the email
address, or a unique constraint on email + name + phone number).

But, if you want to clean this up, and you can write a rule base (e.g. we are
going to select the first phone number when sorted alphanumerically and
discard the rest) then we can write a query to do this for you. If you can't
determine the rule base in advance you're going to have to go through the
data manually and make ad hoc decisions about each set of duplicate data.

HTH

Cheers
Ken

--
www.adOpenStatic.com/cs/blogs/ken/