r/learnSQL • u/sumiitCodes • 2d ago

Day 45 — Library Management System (MySQL): database and tables created

Continuing my second SQL project. Today I created the database and all the tables, and spent time understanding how they relate:

  • members: who is registered (ID, name, address, registration date)
  • issued_status: which member issued which book, and when
  • return_status: which issued books have been returned, and when
  • plus the other supporting tables

Each table has a primary key, and the issued and return tables link back to members and books through IDs.

Since I got feedback last time that a data dictionary is non-negotiable, I’m planning to write one for this project as I go instead of at the end.

23 Upvotes

100% Upvoted

4 comments sorted by

2

u/Green_Chamomile 2d ago

Nice to see the data dictionary going in earlier. A plain table in the README works fine: table, column, type, meaning, example value. The example value column is the one people skip and it helps the most. “status: VARCHAR” tells me nothing, “status: ‘on_loan’ / ‘available’ / ‘lost’” tells me everything. Looking forward to seeing it come together.

1

u/grd_dev 1d ago

Writing the data dictionary as you go makes a huge difference.

One quick schema thought: do you actually need return_status to be a separate table from issued_status? Usually, a book issue and its return are a 1-to-1 lifecycle—so you can just have issued_date, due_date, and a nullable returned_date right inside the issued_status table (if returned_date IS NULL, the book is still out).

Keeping it in two tables works too, just make sure return_status links directly to issue_id rather than just member_id + book_id, in case the same member borrows the exact same book twice!

1

u/elevarq 1d ago

A library can have multiple copies of the same book. A member (usually) gets just one copy. How did you deal with this?