Chacham's Journal: Tape library quick schema

Well, I decided to do the schema. Being used to Oracle, I mostly used the format I used there. Though, since the plan is to use PostgreSQL, I used VARCHAR instead of VARCHAR2. Also, I left out SEQUENCES and TRIGGERS to create the "autonumber", as I need to re-read how PostgreSQL does that.

This is a quick schema, that I haven't gone over to much. The focus is on the inventory, and little on the user except name, number, and history. This is to fit a specific need, though I am trying to keep it broad so it can be morphed as needed, and possibly adapted for others to use without too much work.

Any comments? BTW, DI is Data Integrity. Data integrity is what care for most, so DI constraints are my sanity-checks.

CREATE TABLE Cabinet
(
    Id NUMBER,
    Columns NUMBER,
    Rows NUMBER,
    CONSTRAINT Cabinet_Id_NOT_NULL CHECK (Id IS NOT NULL),
    CONSTRAINT Cabinet_Columns_NOT_NULL CHECK (Columns IS NOT NULL),
    CONSTRAINT Cabinet_Rows_NOT_NULL CHECK (Rows IS NOT NULL),
    CONSTRAINT Cabinet_Id_PK PRIMARY KEY (Id)
);
 
COMMENT ON TABLE Cabinet IS 'Cabinets that hold the copies.';
COMMENT ON COLUMN Cabinet.Id IS 'Unique id for this cabinet.';
COMMENT ON COLUMN Cabinet.Columns IS 'Amount of columns in this cabinet.';
COMMENT ON COLUMN Cabinet.Rows IS 'Amount of rows in this cabinet.';
 
CREATE TABLE Cabinet
(
    Id NUMBER,
    CONSTRAINT Cabinet_Id_NOT_NULL CHECK (Id IS NOT NULL),
    CONSTRAINT Cabinet_Id_PK PRIMARY KEY (Id)
);
 
COMMENT ON TABLE Cabinet IS 'Cabinets that hold the copies.';
COMMENT ON COLUMN Cabinet.Id IS 'Unique id for this cabinet.';
 
CREATE TABLE Status
(
    Id NUMBER,
    Name VARCHAR(0050),
    Description VARCHAR(0255),
    CONSTRAINT Status_Id_NOT_NULL CHECK (Id IS NOT NULL),
    CONSTRAINT Status_Name_NOT_NULL CHECK (Name IS NOT NULL),
    CONSTRAINT Status_Name_UPPER CHECK (Name = UPPER(Name)),
    CONSTRAINT Status_Name_UQ UNIQUE (Name),
    CONSTRAINT Status_Id_PK PRIMARY KEY (Id)
);
 
COMMENT ON TABLE Status IS 'Statuses for the copies.';
COMMENT ON COLUMN Status.Id IS 'Unique id for this status.';
COMMENT ON COLUMN Status.Name IS 'Name for this status.';
COMMENT ON COLUMN Status.Description IS 'Description and notes for this status.';
 
CREATE TABLE Author
(
    Id NUMBER,
    Name VARCHAR(0050),
    Description VARCHAR(00255),
    CONSTRAINT Author_Id_NOT_NULL CHECK (Id IS NOT NULL),
    CONSTRAINT Author_Name_NOT_NULL CHECK (Name IS NOT NULL),
    CONSTRAINT Author_Name_UPPER CHECK (Name = UPPER(Name)),
    CONSTRAINT Author_Name_UQ UNIQUE (Name),
    CONSTRAINT Author_Id_PK PRIMARY KEY (Id)
);
 
COMMENT ON TABLE Author IS 'Authors for the albums.';
COMMENT ON COLUMN Author.Id IS 'Unique id for this author.';
COMMENT ON COLUMN Author.Name IS 'Name for this author.';
COMMENT ON COLUMN Author.Description IS 'Description and notes for this author.';
 
CREATE TABLE Topic
(
    Id NUMBER,
    Name VARCHAR(0050),
    Description VARCHAR(00255),
    CONSTRAINT Topic_Id_NOT_NULL CHECK (Id IS NOT NULL),
    CONSTRAINT Topic_Name_NOT_NULL CHECK (Name IS NOT NULL),
    CONSTRAINT Topic_Name_UPPER CHECK (Name = UPPER(Name)),
    CONSTRAINT Topic_Name_UQ UNIQUE (Name),
    CONSTRAINT Topic_Id_PK PRIMARY KEY (Id)
);
 
COMMENT ON TABLE Topic IS 'Topics for the albums.';
COMMENT ON COLUMN Topic.Id IS 'Unique id for this topic.';
COMMENT ON COLUMN Topic.Name IS 'Name for this topic.';
COMMENT ON COLUMN Topic.Description IS 'Description and notes for this topic.';
 
CREATE TABLE Album
(
    Id NUMBER,
    Name VARCHAR(0050),
    Description VARCHAR(00255),
    Author NUMBER,
    Topic NUMBER,
    CONSTRAINT Album_Id_NOT_NULL CHECK (Id IS NOT NULL),
    CONSTRAINT Album_Name_NOT_NULL CHECK (Name IS NOT NULL),
    CONSTRAINT Album_Author_NOT_NULL CHECK (Author IS NOT NULL),
    CONSTRAINT Album_Name_UPPER CHECK (Name = UPPER(Name)),
    CONSTRAINT Album_Name_UQ UNIQUE (Name),
    CONSTRAINT Album_Author_FK FOREIGN KEY (Author) REFERENCES Author,
    CONSTRAINT Album_Topic_FK FOREIGN KEY (Topic) REFERECNES Topic,
    CONSTRAINT Album_Id_PK PRIMARY KEY (Id)
);
 
COMMENT ON TABLE Album IS 'Albums in the library.';
COMMENT ON COLUMN Album.Id IS 'Unique id for this album.';
COMMENT ON COLUMN Album.Name IS 'Name for this album.';
COMMENT ON COLUMN Album.Description IS 'Description and notes for this album.';
COMMENT ON COLUMN Album.Author IS 'Author of this album.';
COMMENT ON COLUMN Album.Topic IS 'Topic of this album.';
 
CREATE INDEX Album_Author_IX ON Album (Author);
CREATE INDEX Album_Topic_IX ON Album (Topic);
 
CREATE Table Copy
(
    Id NUMBER,
    Album NUMBER,
    Instance NUMBER,
    Copy_Of NUMBER,
    Cabinet NUMBER,
    Column NUMBER,
    Row NUMBER,
    Status NUMBER,
    UPC_Code CHAR(12),
    CONSTRAINT Copy_Id_NOT_NULL CHECK (Id IS NOT NULL),
    CONSTRAINT Copy_Album_NOT_NULL CHECK (Album IS NOT NULL),
    CONSTRAINT Copy_Status_NOT_NULL CHECK (Status IS NOT NULL),
    CONSTRAINT Copy_Column_NOT_NULL CHECK (NOT( (Cabinet IS NOT NULL) AND (Column IS NULL) )),
    CONSTRAINT Copy_Row_NOT_NULL CHECK (NOT( (Cabinet IS NOT NULL) AND (Row IS NULL) )),
    CONSTRAINT Copy_Column_NULL CHECK (NOT( (Cabinet IS NULL) AND (Column IS NOT NULL) )),
    CONSTRAINT Copy_Row_NULL CHECK (NOT( (Cabinet IS NULL) AND (Row IS NOT NULL) )),
    CONSTRAINT Copy_Album_Instance_UQ UNIQUE (Album, Instance),
    CONSTRAINT Copy_Cabinet_Column_Row_UQ UNIQUE (Cabinet, Column, Row),
    CONSTRAINT Copy_UPC_Code_UQ UNIQUE (UPC_Code),
    CONSTRAINT Copy_Cabinet_FK FOREIGN KEY (Cabinet) REFERENCES Cabinet,
    CONSTRAINT Copy_Copy_Of_FK FOREIGN KEY (Copy_Of) REFERENCES Copy,
    CONSTRAINT Copy_Status_FK FOREIGN KEY (Status) REFERENCES Status,
    CONSTRAINT Copy_Id_PK PRIMARY KEY (Id)
);
 
COMMENT ON TABLE Copy IS 'Instance of 'copy' of the album. This is the specific tape.';
COMMENT ON COLUMN Copy.Id IS 'Unique id of this copy.';
COMMENT ON COLUMN Copy.Album IS 'Album this is a copy of.';
COMMENT ON COLUMN Copy.Instance IS 'Instance number of this copy ("copy number").';
COMMENT ON COLUMN Copy.Copy_Of IS 'Parent copy for this copy. If null, this copy is the original.';
COMMENT ON COLUMN Copy.Cabinet IS 'Cabinet this copy is in. If null, it is not assigned to a cabinet.';
COMMENT ON COLUMN Copy.Column IS 'Column of the cabinet this copy is in.';
COMMENT ON COLUMN Copy.Row IS 'Row of the column this copy is in.';
COMMENT ON COLUMN Copy.Status IS 'Status of this copy.';
COMMENT ON COLUMN Copy.UPC_Code IS 'UPC Code of this copy, if one is assigned to it.';
 
CREATE INDEX Copy_Cabinet_IX ON Copy (Cabinet);
CREATE INDEX Copy_Copy_Of_IX ON Copy (Copy_Of);
CREATE INDEX Copy_Status_IX ON Copy (Status);
 
CREATE TABLE Patron
(
    Id NUMBER,
    Name VARCHAR(0050),
    Description VARCHAR(00255),
    Phone NUMBER,
    CONSTRAINT Patron_Id_NOT_NULL CHECK (Id IS NOT NULL),
    CONSTRAINT Patron_Name_NOT_NULL CHECK (Name IS NOT NULL),
    CONSTRAINT Patron_Phone_NOT_NULL CHECK (Phone IS NOT NULL),
    CONSTRAINT Patron_Name_UPPER CHECK (Name = UPPER(Name)),
    CONSTRAINT Patron_Name_UQ UNIQUE (Name),
    CONSTRAINT Patron_Id_PK PRIMARY KEY (Id)
);
 
COMMENT ON TABLE Patron IS 'Patrons borrow the copies.';
COMMENT ON COLUMN Patron.Id IS 'Unique id for this patron.';
COMMENT ON COLUMN Patron.Name IS 'Name for this Patron.';
COMMENT ON COLUMN Patron.Description IS 'Description and notes for this Patron.';
COMMENT ON COLUMN Patron.Phone IS 'Phone number of this patron.';
 
CREATE TABLE Borrow
(
    Id NUMBER,
    Patron NUMBER,
    Copy NUMBER,
    Out_Date DATE DEFAULT(SYSDate),
    To_Be_Returned_Date DATE,
    In_Date DATE,
    CONSTRAINT Borrow_Id_NOT_NULL CHECK (Id IS NOT NULL),
    CONSTRAINT Borrow_Patron_NOT_NULL CHECK (Patron IS NOT NULL),
    CONSTRAINT Borrow_Copy_NOT_NULL CHECK (Copy IS NOT NULL),
    CONSTRAINT Borrow_Out_Date_NOT_NULL CHECK (Out_Date IS NOT NULL),
    CONSTRAINT Borrow_To_Be_Returned_Date_DI CHECK (To_Be_Returned_Date >= In_Date)
    CONSTRAINT Borrow_Out_Date_DI CHECK (Out_Date >= In_Date)
    CONSTRAINT Borrow_Id_PK PRIMARY KEY (Id)
);
 
COMMENT ON TABLE Borrow IS 'Borrowing transactions are listed here.';
COMMENT ON COLUMN Borrow.Id IS 'Unique id for this borrow.';
COMMENT ON COLUMN Borrow.Patron IS 'Patron who is borrwing the copy.';
COMMENT ON COLUMN Borrow.Copy IS 'The copy being borrowed.';
COMMENT ON COLUMN Borrow.Out_Date IS 'Date the borrow transpired and copy was taken.';
COMMENT ON COLUMN Borrow.To_Be_Returned_Date IS 'Date the copy should be returned by.';
COMMENT ON COLUMN Borrow.In_Date IS 'Date the copy was returned.';

reason #4472 why I hate Oracle

[red5]

I left out SEQUENCES and TRIGGERS to create the "autonumber", as I need to re-read how PostgreSQL does that.

Thats what I always hated about Oracle. It dosn't have an autoincrement data type. You have to do it yourself using seqid.nextval. How lame :(

Re:reason #4472 why I hate Oracle

[Chacham]

Thats what I always hated about Oracle. It dosn't have an autoincrement data type. You have to do it yourself using seqid.nextval. How lame :(

Actually, I *love* that about Oracle. Finally, something other than that backwards idea of making a function a data type!

So, using a SEQUENCE was a wonderul idea. I wish they had a way of tying them together, At its very least, allowing for .nextval to be a DEFAULT value. As best, something like:

CREATE TABLE A(ID NUMBER);
CREATE SEQUENCE B FOR A.Id AUTOINCREMENT;

When I used Microsoft products, and had that autoincrementer, it was really bothersome, as it removed control from the user when it incremented. Oracle did a fine job at addressing the issue properly. It just bothers me when a SEQUENCE is used for a table (the overwhelming majority of cases) yet you can only tell that by looking at the TRIGGER.