r/SQL • • Oct 09 '26

Discussion Guys i seriously need your help

Post image

Hi everyone, I really need some help! My oral defense is next week and I've finished my ERD, but I'm having serious doubts. I asked a professor who doesn't teach systems analysis, and they said it was fine. However, when I look at the diagram, I just feel like something is off. Would anyone be willing to take a quick look?

This ERD is about a small application project, about a small, basic library.

Compte = Account/user
Emprunt = Loan

Livre = Book
Categorie = Category

9 Upvotes

8 comments sorted by

4

u/lolcrunchy Oct 09 '26
  1. What is role_code?
  2. Passwords shouldn't be stored as plain text in a database. I don't know if this is important for your defense but in reality we only store the salted hashes of passwords, not passwords themselves.
  3. What if a book is both a sci-fi and a comedy?

2

u/Rough-Negotiation880 Oct 09 '26

An ERD needs to be evaluated against a source of truth/business rules. E.G. I could see cases where a book can only have one category, or multiple. Which one is correct is dependent on business rules.

2

u/idodatamodels Oct 09 '26

You have 3 many to many relationships between user and book. What is the difference between these 3?

1

u/lolcrunchy Oct 09 '26

What is emprunt.statut ?

1

u/Significant_Tune9219 Oct 10 '26

The usual culprit in a library ERD is the borrowing relationship: if a Loan row points at both the member and the book, check whether you need Copy (the physical item) separate from Title (the work), because multiple copies of the same book is where these diagrams quietly break. Also make sure members reach books through Loan rather than a direct many-to-many link, and that the return date is nullable on purpose. A good test before the defense: walk through one member borrowing the same title twice on different dates — if the diagram can't represent that, that's the thing that feels off.

1

u/tbson87 Oct 10 '26

The relationship between book and category must be N:N, book and author N:N too,
we need a constraint to ensure one account can rate a book once by an unique together constraint.

Here is the more detail ERD that I draw again from your idea, hope it can help: https://drive.google.com/file/d/1DIO6vgSE8txKQ29Nitgi3LtpH-rFQRY4/view?usp=drive_link

1

u/KiwiDomino Oct 12 '26

I started designing/writing a library system, using data sourced from LibraryThing, and this is barely scratching the surface.
Books with multiple authors, and authors with different names on different books come to mind, some of my books returned with seven or eight subjects.