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

Darn pop singers!

From: Ian Pilcher <i(dot)pilcher(at)comcast(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Darn pop singers!
Date: 2004-05-11 13:48:31
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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:

     'SELECT $1 = (SELECT real_id FROM person_info WHERE id = $1) AS

ALTER TABLE person_info ADD CHECK (id = real_id OR

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


Ian Pilcher                                        i(dot)pilcher(at)comcast(dot)net


pgsql-novice by date

Next:From: Jon PoultonDate: 2004-05-11 14:18:20
Subject: Re: Darn pop singers!
Previous:From: Tom LaneDate: 2004-05-10 23:43:25
Subject: Re: Changing a relation's name in parser stage

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