Database design changes

Sprint2_Create_table.sql

Earlier we were having Institution details for any Book when User searched about the Book, Now we have created the table named 'ims_location_t' where we have added the location details linked to it which shows the location of that particular Book i.e. Where the Book exists. The changes added to the table are shown below.

Benefit -When User look for any Book, the results are displaying the location also for that Book.

There is one more table created named ‘scsb_properties_t’ which is giving information about the properties which were earlier maintained externally, Now the external properties files are moved to the Database.

Benefit - When User wants to know the properties, it is fetching the details about it through this table.

--liquibase formatted sql
--changeset Moses:1

use recap;

CREATE TABLE IF NOT EXISTS ims_location_t (
ims_location_id INT NOT NULL AUTO_INCREMENT,
ims_location_code varchar(45) NOT NULL UNIQUE,
ims_location_name varchar(255) NOT NULL, 
description  varchar(255) ,
active char(1)  NOT NULL,
created_by varchar(255) NOT NULL,
created_date timestamp NOT NULL ,
updated_by  varchar(255) NOT NULL ,
updated_date timestamp NOT NULL,
PRIMARY KEY ( ims_location_id )
);

CREATE TABLE IF NOT EXISTS scsb_properties_t (
scsb_properties_id INT NOT NULL AUTO_INCREMENT,
p_key varchar (255) NOT NULL ,
p_value varchar(1500) NOT NULL  ,
description  varchar(255) ,
institution_code  varchar(50) ,
ims_location_code varchar(50) ,
active char(1)  NOT NULL,
created_date timestamp NOT NULL ,
updated_date timestamp ,
created_by  varchar(255) NOT NULL,
updated_by  varchar(255) ,
PRIMARY KEY ( scsb_properties_id )
);

CREATE TABLE IF NOT EXISTS Institution_ims_location_t (
Institution_ims_location_id   INT NOT NULL AUTO_INCREMENT,
institution_id  INT  NOT NULL UNIQUE,
ims_location_id INT NOT NULL, 
active char(1)  NOT NULL,
created_by varchar(255) NOT NULL,
created_date timestamp NOT NULL ,
updated_by  varchar(255) NOT NULL ,
updated_date timestamp NOT NULL,
PRIMARY KEY ( Institution_ims_location_id ),
FOREIGN KEY (institution_id) REFERENCES institution_t (institution_id),
FOREIGN KEY (ims_location_id) REFERENCES ims_location_t (ims_location_id)
);

CREATE TABLE IF NOT EXISTS generic_patron_t (
generic_patron_id   INT NOT NULL AUTO_INCREMENT,
requesting_inst_id  INT  NOT NULL UNIQUE,
item_own_inst_id INT NOT NULL, 
edd_generic_patron varchar(20) ,
retrieval_generic_partron varchar(20),
created_by varchar(255) NOT NULL,
created_date timestamp NOT NULL ,
updated_by  varchar(255) NOT NULL ,
updated_date timestamp NOT NULL,
PRIMARY KEY ( generic_patron_id ),
FOREIGN KEY (requesting_inst_id) REFERENCES institution_t (institution_id),
FOREIGN KEY (item_own_inst_id) REFERENCES institution_t (institution_id)
);

Sprint2_Alter_table.sql

--liquibase formatted sql
--changeset Moses:2

use recap;

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE item_t ADD  ims_location_id  INT Not Null ,
ADD FOREIGN KEY (ims_location_id) REFERENCES ims_location_t(ims_location_id);

SET FOREIGN_KEY_CHECKS=1;

ALTER TABLE Institution_t ADD  ils_protocol varchar(10) Not Null;