-- -- Library system data model -- -- ryanlee@arsdigita.com, 8/30/2000 -- create sequence library_id_seq start 1; create table library ( -- primary key for books book_id integer not null primary key, -- main title, not including refernces to edition, etc. title varchar(4000) not null, -- which edition is it edition integer, -- unique identifier for book purchasing isbn varchar(10) not null, -- date published pub_date date, -- number of pages pages integer ); create sequence library_author_id_seq start 1; create table library_authors ( -- primary key for authors author_id integer not null primary key, -- name author_name varchar(1000) not null ); create sequence library_publisher_id_seq start 1; create table library_publishers ( -- primary key for publishers publisher_id integer not null primary key, -- publisher name publisher_name varchar(1000) not null ); -- map between authors, books, and publishers create table book_author_map ( -- the id of the book book_id integer references library, -- the id of the author author_id integer references library_authors, constraint bk_at_unique unique(book_id, author_id) ); create table book_pub_map ( -- the id of the book book_id integer references library, -- the id of the publisher publisher_id integer references library_publishers, constraint bk_pub_unique unique(book_id, publisher_id) ); create sequence user_book_map_seq start 1; -- there is no uniqueness constraint here; people can own -- more than one copy create table user_book_map ( -- primary key for this table map_id integer primary key not null, -- the id of the book book_id integer references library, -- the owner of the book user_id integer references users, -- the status of their copy status varchar(30) not null, constraint bk_status_chk check (status in ('new','good','used','poor','bad')), -- does the rest of the world know they have this book? private_p char(1) default 'f', constraint bk_owner_private_chk check (private_p in ('t','f')), borrowable_p char(1) default 't', constraint bk_owner_borrowable_chk check (borrowable_p in ('t','f')), auth_needed_p char(1) default 'f', constraint bk_owner_auth_chk check (auth_needed_p in ('t','f')) ); create table book_synopsis ( -- synopsis id book_synopsis_id integer not null primary key, -- reference the book id book_id integer not null references library, -- who wrote this synopsis user_id integer not null references users, creation_date timestamp not null default now(), last_modified timestamp ); create sequence owner_prefs_id_seq; -- preferences create table owner_prefs ( prefs_id integer not null primary key, owner_id integer not null references users, -- assess fines? fines_p char(1) not null default 'f', constraint owner_prefs_fines_p_chk check (fines_p in ('t','f')), -- size of fine, if applicable fine_size numeric, -- rate of colletion (per day, per week, per month, absolute total) fine_rate varchar(30) default 'total', constraint owner_prefs_fine_rate_chk check (fine_rate in ('total','day','week','month')), -- limit in days borrow_limit integer not null default 0 ); -- categorize with aD categorization system -- look up book info on a .com and stuff it here for faster -- access to information -- create table library_lookup_cache ( -- ); -- figure out the true first letter of a proper title CREATE FUNCTION true_first_letter( integer ) RETURNS varchar AS 'declare v_title text; v_char varchar; v_book_id alias for $1; begin SELECT title INTO v_title FROM library WHERE book_id = v_book_id; v_char := substr(v_title, 1, 1); IF length(v_title) > 4 THEN IF lower(substr(v_title, 1, 4)) = ''the ''::text THEN v_char := substr(v_title, 5, 1); ELSE IF lower(substr(v_title, 1, 3)) = ''an ''::text THEN v_char := substr(v_title, 4, 1); ELSE IF lower(substr(v_title, 1, 2)) = ''a ''::text THEN v_char := substr(v_title, 3, 1); END IF; END IF; END IF; END IF; return v_char; end;' LANGUAGE 'plpgsql';