Darn pop singers! - Mailing list pgsql-novice

From Ian Pilcher
Subject Darn pop singers!
Date
Msg-id 40A0D9AF.6000305@comcast.net
Whole thread Raw
Responses Re: Darn pop singers!
Re: Darn pop singers!
List pgsql-novice
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
========================================================================


pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: Changing a relation's name in parser stage
Next
From: "Jon Poulton"
Date:
Subject: Re: Darn pop singers!