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 (AT) comcast (DOT) 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

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 (AT) postgresql (DOT) org
[mailtogsql-novice-owner (AT) postgresql (DOT) org]On Behalf Of Ian Pilcher
Sent: 11 May 2004 14:49
To: pgsql-novice (AT) postgresql (DOT) 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 (AT) comcast (DOT) 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




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Ian Pilcher wrote:

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.

checker than "perl".
-- An unknown poster and Fredrik Lundh

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Oliver Fromme wrote:
Thank you! I hadn't thought of using a view, but it does seem like the
right solution here.

--
================================================== ======================
Ian Pilcher i.pilcher (AT) comcast (DOT) net
================================================== ======================


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings