Обсуждение: 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
========================================================================
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
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
Oliver Fromme wrote: > > 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. > Thank you! I hadn't thought of using a view, but it does seem like the right solution here. -- ======================================================================== Ian Pilcher i.pilcher@comcast.net ========================================================================