CREATE TABLE subscribers
( subscriber_id    NUMBER CONSTRAINT sub_pk PRIMARY KEY
, name             VARCHAR2(40) NOT NULL )
ORGANIZATION INDEX
MONITORING;

CREATE TABLE subscriptions
( subscription_id  NUMBER CONSTRAINT sus_pk PRIMARY KEY
, subscriber_id    CONSTRAINT sus_sub_fk REFERENCES subscribers
, start_date       DATE NOT NULL
, end_date         DATE
, status           VARCHAR2(1)  <i>-- Maybe this should be FK to a 'Subscription Statuses' table?</i>
, CONSTRAINT sus_status_chk CHECK (status IN ('A','B','C') )
, CONSTRAINT sus_date_chk CHECK (end_date >= start_date ) )
ORGANIZATION INDEX
MONITORING;

CREATE TABLE subscribers
( subscriber_id    NUMBER CONSTRAINT sub_pk PRIMARY KEY
, name             VARCHAR2(40) NOT NULL )
ORGANIZATION INDEX
MONITORING;

CREATE TABLE publications
( publication_id   NUMBER CONSTRAINT pub_pk PRIMARY KEY
, name             VARCHAR2(40) NOT NULL CONSTRAINT pub_name_uk UNIQUE )
ORGANIZATION INDEX
MONITORING;

CREATE TABLE subscriptions
( subscription_id  NUMBER CONSTRAINT sus_pk PRIMARY KEY
, subscriber_id    CONSTRAINT sus_sub_fk REFERENCES subscribers
, publication_id   CONSTRAINT sus_pub_fk REFERENCES publications
, start_date       DATE NOT NULL
, end_date         DATE
, status           VARCHAR2(1)
, CONSTRAINT sus_pub_uk UNIQUE (subscriber_id, publication_id, start_date)
, CONSTRAINT sus_status_chk CHECK (status IN ('A','B','C') )
, CONSTRAINT sus_date_chk CHECK (end_date >= start_date ) )
ORGANIZATION INDEX
MONITORING;