[OPEN-ILS-DEV] ***SPAM*** Proposed serial.caption_and_pattern table

[Scott McKellar]

Over the last couple of weeks another serial table has been proposed, named serial.caption_and_pattern, featuring certain details derived from the MFHD record.

Here's my understanding of what that table should look like, but I'd like some feedback before I commit anything:

CREATE TABLE serial.caption_and_pattern (
    id           SERIAL     PRIMARY KEY,
    record       BIGINT     NOT NULL
                            REFERENCES serial.record_entry (id)
                            ON DELETE CASCADE
                            DEFERRABLE INITIALLY DEFERRED,
    type         TEXT       NOT NULL
                            CONSTRAINT cap_type CHECK ( type in
                           ( 'basic', 'supplement', 'index' )),
    active       BOOL       NOT NULL DEFAULT FALSE,
    pattern_code TEXT       NOT NULL,       -- must contain JSON
    enum_1       TEXT,
    enum_2       TEXT,
    enum_3       TEXT,
    enum_4       TEXT,
    enum_5       TEXT,
    enum_6       TEXT,
    chron_1      TEXT,
    chron_2      TEXT,
    chron_3      TEXT,
    chron_4      TEXT,
    chron_5      TEXT,
    CONSTRAINT type_once_per_record UNIQUE (record, type)
);

Questions:

1. Are all the right things nullable or not nullable?

2. Is the ON DELETE CASCADE appropriate?

3. Is there any use, or need, for a create_time column?

4. The UNIQUE constraint has not been discussed before, so far as I know -- I just made it up.  Is it reasonable?  Or should (record + type) be unique only where active is true (which we could enforce using a unique index with a WHERE clause)?  Or is no such form of uniqueness desirable?

Finally, one more question, about a different table.  In serial.record_entry, the "marc" column is currently NOT NULL.  Do we now want to make it nullable?

Scott McKellar