Skip site navigation (1) Skip section navigation (2)

Re: Darn pop singers!

From: Oliver Fromme <olli(at)lurza(dot)secnetix(dot)de>
To: i(dot)pilcher(at)comcast(dot)net (Ian Pilcher)
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Darn pop singers!
Date: 2004-05-11 14:31:43
Message-ID: 200405111431.i4BEVhSw010807@lurza.secnetix.de (view raw or flat)
Thread:
Lists: pgsql-novice
Ian Pilcher wrote:
 > 
 >   id | real_id | last_name  | first_name
 > ----+---------+------------+------------
 >    4 |       4 | Jones      | David
 >    5 |       4 | Bowie      | David
 > 
 > David Jones is a "real" name, because id = real_id.  I do not want to
 > allow Arnold Corns to be added with real_id = 5 (since David Bowie is
 > not a "real" name), so I've done this:
 > 
 > [CREATE FUNCTION + ADD CHECK]
 > 
 > I can't help feeling, however, that this is a bit kludgy.  Can anyone
 > suggest a better way?


I'm not sure if my solution would be less kludgy, but this
is how I would have done it:

Create two tables, one for the real names, and one for the
alias names.  The real_persons table would look like this:

  real_id | last_name  | first_name
 ---------+------------+------------
        4 | Jones      | David

And the aliases table would look like this:

  alias_id | real_id | last_name  | first_name
 ----------+---------+------------+------------
         5 |       4 | Bowie      | David

Of course, aliases.real_id should be made a foreign key to
real_persons.real_id, so you cannot enter an alias for a
real person which doesn't exist.  Note that alias_id and
real_id are completely separate -- if you want to make them
disjunct (for safety, so you can't mix them up), modify the
sequences so that one produces only odd numbers and the
other one only even numbers.  Or make one start at 10000000
or whatever.

You can conveniently create a view in order to merge real
persons and aliases together.

Best regards
   Oliver

-- 
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 M√ľnchen
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

 > Can the denizens of this group enlighten me about what the
 > advantages of Python are, versus Perl ?
"python" is more likely to pass unharmed through your spelling
checker than "perl".
        -- An unknown poster and Fredrik Lundh

In response to

Responses

pgsql-novice by date

Next:From: Noel FauxDate: 2004-05-12 06:58:52
Subject: pg_dump problem
Previous:From: Jon PoultonDate: 2004-05-11 14:18:20
Subject: Re: Darn pop singers!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group