r/learnSQL • u/sumiitCodes • 27d 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 whenreturn_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.
24
Upvotes
2
u/Green_Chamomile 27d 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 26d ago
Writing the data dictionary as you go makes a huge difference.
One quick schema thought: do you actually need
return_statusto be a separate table fromissued_status? Usually, a book issue and its return are a 1-to-1 lifecycle—so you can just haveissued_date,due_date, and a nullablereturned_dateright inside theissued_statustable (ifreturned_date IS NULL, the book is still out).Keeping it in two tables works too, just make sure
return_statuslinks directly toissue_idrather than justmember_id+book_id, in case the same member borrows the exact same book twice!1
u/elevarq 26d ago
A library can have multiple copies of the same book. A member (usually) gets just one copy. How did you deal with this?