TIA to anyone who reads all the way through this.

I'm continuing to work on the database design for my super-, duper-,
uber-web jukebox. One of my goals is to have a very flexible data model
that allows me to capture the constantly changing lineups of pop bands,
jazz ensembles, etc.

With this in mind, I've come up with the following:

--
--
-- Basic information about individual persons
--
--

CREATE SEQUENCE person_name_id_seq START 1;
CREATE TABLE person_names (
id INTEGER DEFAULT nextval('person_name_id_seq') PRIMARY KEY,
last_name TEXT NOT NULL,
first_name TEXT DEFAULT '' NOT NULL,
middle_name TEXT DEFAULT '' NOT NULL,
CHECK ((id = 0) = (last_name = ''))
);

-- "empty" name for "empty" person
INSERT INTO person_names (id, last_name) VALUES (0, '');

CREATE SEQUENCE person_id_seq START 1;
CREATE TABLE persons (
id INTEGER DEFAULT nextval('person_id_seq') PRIMARY KEY,
given_name INTEGER CHECK (given_name != 0) REFERENCES person_names,
legal_name INTEGER CHECK (legal_name != 0) REFERENCES person_names,
primary_name INTEGER NOT NULL REFERENCES person_names,
CHECK ((id = 0) = (primary_name = 0))
);

-- "empty" person for "empty" stage name
INSERT INTO persons (id, primary_name) VALUES (0, 0);

CREATE SEQUENCE stage_name_id_seq START 1;
CREATE TABLE stage_names (
id INTEGER DEFAULT nextval('stage_name_id_seq') PRIMARY KEY,
person INTEGER NOT NULL REFERENCES persons,
name INTEGER NOT NULL REFERENCES person_names,
CHECK ((id = 0) = (person = 0) AND (id = 0) = (name = 0)),
UNIQUE (person, name)
);

-- "empty" stage name for artists that are groups
INSERT INTO stage_names (id, person, name) VALUES (0, 0, 0);


--
--
-- Basic information about groups
--
--

CREATE SEQUENCE group_name_id_seq START 1;
CREATE TABLE group_names (
id INTEGER DEFAULT nextval('group_name_id_seq') PRIMARY KEY,
name TEXT NOT NULL,
prefix TEXT DEFAULT '' NOT NULL,
CHECK ((id = 0) = (name = ''))
);

-- "empty" group name for unnamed collaborations
INSERT INTO group_names (id, name) VALUES (0, '');

CREATE SEQUENCE lineup_id_seq START 1;
CREATE TABLE lineups (
id INTEGER DEFAULT nextval('lineup_id_seq') PRIMARY KEY,
name INTEGER NOT NULL REFERENCES group_names
);

-- "empty" lineup for artists that are persons
INSERT INTO lineups (id, name) VALUES (0, 0);


--
--
-- An artist is a person or a group
--
--

CREATE SEQUENCE artist_id_seq;
CREATE TABLE artists (
id INTEGER DEFAULT nextval('artist_id_seq') PRIMARY KEY,
person INTEGER DEFAULT 0 NOT NULL REFERENCES stage_names,
lineup INTEGER DEFAULT 0 NOT NULL REFERENCES lineups,
CHECK ((person = 0) != (lineup = 0)),
UNIQUE (person, lineup)
);


--
--
-- A lineup must have two or more artists (each of which may or may not
-- have a defined role) or a name.
--
--

CREATE SEQUENCE role_id_seq;
CREATE TABLE roles (
id INTEGER DEFAULT nextval('role_id_seq') PRIMARY KEY,
name TEXT NOT NULL UNIQUE CHECK (name != '')
);

CREATE SEQUENCE artist_role_lineup_id_seq;
CREATE TABLE artist_role_lineup (
id INTEGER DEFAULT nextval('artist_role_lineup_id_seq') PRIMARY KEY,
artist INTEGER NOT NULL CHECK (artist != 0) REFERENCES artists,
role INTEGER REFERENCES roles,
lineup INTEGER NOT NULL CHECK (lineup != 0)
REFERENCES lineups DEFERRABLE
);

CREATE FUNCTION lineup_has_artists(INTEGER) RETURNS BOOLEAN AS
'SELECT (SELECT count(*) FROM artist_role_lineup WHERE lineup = $1)

ALTER TABLE lineups ADD CHECK (
CASE
WHEN id = 0 THEN (name = 0)
ELSE (name != 0 OR lineup_has_artists(id))
END
);


Creating a group with no membership information is straightforward:

=> INSERT INTO group_names (name, prefix) VALUES ('Police', 'The');
INSERT 34562 1
=> SELECT * FROM group_names;
id | name | prefix
----+--------+--------
0 | |
1 | Police | The
(2 rows)

=> INSERT INTO lineups (name) VALUES (1);
INSERT 34563 1
=> SELECT * FROM lineups;
id | name
----+------
0 | 0
1 | 1
(2 rows)

=> INSERT INTO artists (lineup) VALUES (1);
INSERT 34564 1
pilcher=> SELECT * FROM artists;
id | person | lineup
----+--------+--------
1 | 0 | 1
(1 row)


Adding information about individuals is also pretty simple:

=> INSERT INTO person_names (last_name, first_name)
-> VALUES ('Fitzgerald', 'Ella');
INSERT 34713 1
=> INSERT INTO person_names (last_name, first_name)
-> VALUES ('Armstrong', 'Louis');
INSERT 34714 1
=> SELECT * FROM person_names;
id | last_name | first_name | middle_name
----+------------+------------+-------------
0 | | |
1 | Fitzgerald | Ella |
2 | Armstrong | Louis |
(3 rows)

=> INSERT INTO persons (primary_name) VALUES (1);
INSERT 34715 1
=> INSERT INTO persons (primary_name) VALUES (2);
INSERT 34716 1
=> SELECT * FROM persons;
id | given_name | legal_name | primary_name
----+------------+------------+--------------
0 | | | 0
1 | | | 1
2 | | | 2
(3 rows)

=> INSERT INTO stage_names (person, name) VALUES (1, 1);
INSERT 34717 1
=> INSERT INTO stage_names (person, name) VALUES (2, 2);
INSERT 34718 1
=> SELECT * FROM stage_names;
id | person | name
----+--------+------
0 | 0 | 0
1 | 1 | 1
2 | 2 | 2
(3 rows)

=> INSERT INTO artists (person) VALUES (1);
INSERT 34719 1
=> INSERT INTO artists (person) VALUES (2);
INSERT 34720 1
=> SELECT * FROM artists;
id | person | lineup
----+--------+--------
1 | 0 | 1
2 | 1 | 0
3 | 2 | 0
(3 rows)


Now, however, I want to create an "unnamed" collaboration between Ella
Fitzgerald and Louis Armstrong:

=> BEGIN;
BEGIN
=> SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
=> SELECT nextval('lineup_id_seq');
nextval
---------
2
(1 row)

=> INSERT INTO artist_role_lineup (artist, lineup)
-> VALUES (2, 2);
INSERT 34721 1
=> INSERT INTO artist_role_lineup (artist, lineup)
-> VALUES (3, 2);
INSERT 34722 1
=> INSERT INTO lineups (id, name) VALUES (2, 0);
INSERT 34723 1
=> COMMIT;
COMMIT
=> SELECT * FROM lineups;
id | name
----+------
0 | 0
1 | 1
2 | 0
(3 rows)

=> SELECT * FROM artist_role_lineup WHERE lineup = 2;
id | artist | role | lineup
----+--------+------+--------
1 | 2 | | 2
2 | 3 | | 2
(2 rows)

=> INSERT INTO artists (lineup) VALUES (2);
INSERT 34724 1
=> SELECT * FROM artists;
id | person | lineup
----+--------+--------
1 | 0 | 1
2 | 1 | 0
3 | 2 | 0
4 | 0 | 2
(4 rows)

For some reason, I find it very counter-intuitive to create the
artist_role_lineup entries before creating the lineup itself. I believe
that I'm stuck with approach, because PostgreSQL doesn't support
deferrable check constraints.

To my questions (finally!):

1. Is there a better approach to working around the lack of deferrable
check constraints? (I would consider an approach that allows me to
create the lineup before the artist_role_lineup entries to be better
-- assuming that the workaround doesn't create more obfuscation than
it removes.)

2. If the answer to #1 is no, how can I encapsulate the steps necessary
to create an unnamed lineup into some sort of function/procedure?
Everything I've read seems to assume that a function must return
some value and be invoked through a SELECT statement. I would like
to be able to simply:

create_unnamed_lineup(artist, artist)

It should either succeed or throw(?) an error. (I can always add
additional artists to a lineup later.)

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


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