Database Design Challenge

Format: Design a data model (entity-relationship diagram) for a given scenario, then have AI review it.

Scenario 1: Library Management System Needs to manage: books, patrons, borrowing records, fines, reservations

You need to answer:

• What are the "entities" (tables)?
• What are the relationships between them? (One-to-one? One-to-many? Many-to-many?)
• What are the key fields for each entity?
• If a patron borrows a book, returns it, then borrows it again, how is the data stored?

Scenario 2: E-Commerce Order System Needs to manage: products, SKUs (size/color variants), shopping cart, orders, payments, refunds

Key questions:

• What's the relationship between products and SKUs?
• What's the relationship between orders and payments? (One-to-one? Can one order have multiple payment attempts?)
• Is a refund a new record or a modification to the original order?

Scenario 3: Social Media Needs to manage: users, posts, comments, likes, follows, messages

Key questions:

• How do you store the "follow" relationship? (User A follows User B ≠ User B follows User A)
• Should likes be a separate table or a count field on posts?
• When deleting a user, what happens to their posts and comments?

Verification Method: After drawing, ask Claude: "Review this data model and find three potential problems."