Library

books, authors, members and loans. 8 tables, 27 columns, no data.

The map

Map of the 8 tables and their linksN1N1N1N1N1N1N1authorsPKidbigintnamevarcharpublishersPKidbigintnamevarcharmembersPKidbigintfull_namevarcharemailvarcharjoined_attimestamptzbooksPKidbiginttitlevarcharisbnvarcharFKpublisher_idbigintpublished_yearsmallintbook_authorsFKbook_idbigintFKauthor_idbigintcopiesPKidbigintFKbook_idbigintshelfvarcharloansPKidbigintFKcopy_idbigintFKmember_idbigintborrowed_ondatereturned_ondatefinesPKidbigintFKloan_idbigintamountnumericpaidboolean

Each line runs from a linking column (N) to the key it points at (1). The same tables are listed below as text.

The tables

authors

  • id bigintPK
  • name varchar(200) required

publishers

  • id bigintPK
  • name varchar(200) required

books

  • id bigintPK
  • title varchar(300) required
  • isbn varchar(20)
  • publisher_id bigint→ publishers
  • published_year smallint

book_authors

  • book_id bigint→ books required
  • author_id bigint→ authors required

copies

  • id bigintPK
  • book_id bigint→ books required
  • shelf varchar(20) required

members

  • id bigintPK
  • full_name varchar(200) required
  • email varchar(255)
  • joined_at timestamptz required

loans

  • id bigintPK
  • copy_id bigint→ copies required
  • member_id bigint→ members required
  • borrowed_on date required
  • returned_on date

fines

  • id bigintPK
  • loan_id bigint→ loans required
  • amount numeric(12,2) required
  • paid boolean required

What you could look at in SSDO

Create a free workspace and open this sample   All samples

PK is a primary key. “→ table” is a link (a foreign key) to that table. This page is a description of a structure we wrote; it holds no rows.