Re: Darn pop singers! - Mailing list pgsql-novice

Why not simply have a 'Stage name' column, which may or may not be the same
as the real name? Or perhaps an 'alias' column, which is of type TEXT, which
contains a comma separated list of aliases? Also- you cant be sure that the
firstname/lastname combination is unique, so I wouldnt have this as a
constraint.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Ian Pilcher
Sent: 11 May 2004 14:49
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Darn pop singers!


Apologies in advance if this is a dupe....

Sorry if this is a FAQ.  My Google skills are failing me.

I'm in the initial stages of designing a database for my super-, ultra-,
uber-web jukebox application -- which is really a vehicle for me to
learn RDBMSes, SQL, JSPs, servlets, etc.  Right now, I'm trying to
come up with the best way to model musical artists -- singers, song-
writers, etc.

The problem is that these people have an annoying habit performing under
multiple stage names.  David Bowie, born David Jones, also released
music under the name Arnold Corns.

Here is my current approach:

CREATE SEQUENCE person_info_id_seq;

CREATE TABLE person_info (
     id INTEGER DEFAULT nextval('person_info_id_seq') PRIMARY KEY,
     real_id INTEGER DEFAULT currval('person_info_id_seq') NOT NULL
         REFERENCES person_info,
     last_name TEXT NOT NULL,
     first_name TEXT DEFAULT '' NOT NULL,
     UNIQUE (last_name, first_name)
);

So I might have this:

  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 person_is_real(INTEGER) RETURNS BOOLEAN AS
     'SELECT $1 = (SELECT real_id FROM person_info WHERE id = $1) AS
         RESULT;'
     LANGUAGE SQL
;

ALTER TABLE person_info ADD CHECK (id = real_id OR
     person_is_real(real_id));

I can't help feeling, however, that this is a bit kludgy.  Can anyone
suggest a better way?

Thanks!

--
========================================================================
Ian Pilcher                                        i.pilcher@comcast.net
========================================================================


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match