Re: DDL problems: Referential issue?

On Wed, Nov 4, 2009 at 1:14 PM, Leif Biberg Kristensen
<leif@solumslekt.org> wrote:
> On Wednesday 4. November 2009 21.03.26 Scott Marlowe wrote:
>> On Wed, Nov 4, 2009 at 11:53 AM, Leif Biberg Kristensen
>> > This looks strange to me, but it works:
>> >
>> > pgslekt=> CREATE TABLE participant_notes (
>> > pgslekt(>     person_fk   INTEGER NOT NULL,
>> > pgslekt(>     event_fk    INTEGER NOT NULL,
>> > pgslekt(>     part_note   TEXT,
>> > pgslekt(>     PRIMARY KEY (person_fk, event_fk),
>> > pgslekt(>     FOREIGN KEY (person_fk, event_fk) REFERENCES participants
>> > (person_fk, event_fk)
>> > pgslekt(> );
>> > NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
>> > "participant_notes_pkey" for table "participant_notes"
>> > CREATE TABLE
>>
>> Note that this will limit you to one record in your participant notes
>> for each record in the participants table.
>
> That's exactly what I want :)
>
> For all practical purposes, the design is equivalent to adding a TEXT column
> to the participants table. But as I expect a very small number of notes
> compared to the number of rows in the participants table, I prefer to create a
> small extra table rather than having a large number of null values in the
> participants table. Performance-wise, it probably doesn't matter much. It's
> more a matter of taste.

Exactly.  Note that null values i pgsql take up VERY little space, so
performance-wise one table is likely faster, but it's not gonna break
the bank to have two.