1

Imagine these different cases when designing a database:

Case 1:

One book (only one instance of a title physically) can be borrowed by many people, but only one person at once but many people during a time period.

Case 2:

Multiple instances of the same book physically (same title) can be borrowed by many people at the same time (obviously) in the one and same library only as before.

Would these two cases produce the same result when designing a relational schema?

If someone comes up with a better title for this question I will gratefully accept a change.

Improve this question
3
  • What do you mean by "same result"? Your "requirements" are insufficiently detailed to design anything.
    – mustaccio
    Commented Feb 13, 2020 at 20:40
  • @mustaccio I mean in terms of cardinality when designing a relational schema.
    – Mister Equis
    Commented Feb 13, 2020 at 20:45
  • 1
    Can you clarify your question? The 'result' will depend on the query and the underlying relational schema. Your two scenarios are not defining a schema. For example, Case 2 could use an identical table to Case 1 but include a Qty Available column to is updated every time a book is checked out and in, and this would of course affect the cardinality of any join between the Books and Checkouts tables. Or you may have a row per physical copy of the book, or a separate BookCopies table linked to the Book record that contains the records of the copies.
    – HandyD
    Commented Feb 14, 2020 at 5:38

1 Answer 1

Reset to default
4

It's common to confuse a Thing (a physical book) with the Specification of that Thing (the idea of the book)

Say you have a Book Specification:

Title: Moby Dick
Format: Hard Cover
ISBN: 0840769008

Then you also have a Book Asset:

ISBN: 0840769008 (refers to Specification)
Serial Number: 1234
Condition: poor

People can borrow a Book Asset (I borrowed a particular copy).

People can reserve a Book Specification (I want to borrow that book but I don't care which copy I get)

So in your case, for one book, you'd have a specification for it, and one asset. For multiple, you'd have one specification and multiple assets

You'd design the same schema for both scenarios, by using Specification has many Assets, Asset belongs to Specification

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.