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

[Mike Rylander]

On Wed, Jun 2, 2010 at 5:52 PM, Scott McKellar <mck9 at swbell.net> wrote:
> 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)
> );
>

For posterity, the answers in IRC this afternoon were:

> Questions:
>
> 1. Are all the right things nullable or not nullable?

Yes.

>
> 2. Is the ON DELETE CASCADE appropriate?

Yes.

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

Maybe. (I vote yes, FWIW.  Then we can order them chronologically.)

>
> 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?
>

No UNIQUE constraint is desired.

> 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?
>

No answer in IRC, but I think "yes".

Thanks, Dan and Scott!

-- 
Mike Rylander
 | VP, Research and Design
 | Equinox Software, Inc. / The Evergreen Experts
 | phone:  1-877-OPEN-ILS (673-6457)
 | email:  miker at esilibrary.com
 | web:  http://www.esilibrary.com