Re: Simple db table design question

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 18 Mar 2003 12:32:31 -0800
Message-ID: <c0d87ec0.0303181232.3621f3a8@posting.google.com>

>> METHOD 2 HomeEmail and WorkEmail are VarChar fields [sic]
containing email addresses BestEmail is a Varchar field [sic] containing "Home" or "Work" <<

Don't use VARCHAR(n) when a simple code will do. If you truly have only two options and they are always NOT NULL, then

CREATE TABLE Foobar
(name ....

 home_email VARCHAR(64) NOT NULL,
 work_email VARCHAR(64) NOT NULL,
 best_email CHAR(1) DEFAULT 'h'
         CHECK (best_email IN ('h','w')),

 ...);

SELECT ... CASE WHEN best_email = 'h'

                THEN home_email ELSE work_email END AS email,

  FROM Foobar
 WHERE ...; Received on Tue Mar 18 2003 - 14:32:31 CST