Library Schema (1 Viewer)

[studiesofboard]

Since this is the only IPT thread that is currently active can someone tell me whether this is a good schema for a library system. If not how can this schema be improved.

 

[Drdusk]

Since this is the only IPT thread that is currently active can someone tell me whether this is a good schema for a library system. If not how can this schema be improved.
View attachment 27982

As I have said before you don't need to post in an 'active' thread. Just make your own thread. We can still see it, it doesn't matter where you post it so please create a new thread in the IPT forum for a question. This makes it easier for other students who may have the same question to also see peoples responses. In fact if you want a response it's probably better to create your own thread.

As for the schema, I actually don't remember anymore how to make one sadly.

 

[brent012]

Not sure what you get taught in IPT, but just some general comments related to databases - some of these points are likely closer to the university or industry level but it's still some food for thought.

1. What are the keys of the entities involved?
2. What is "contact details"? I think this needs splitting into whatever you care about. Consider that the library probably sends out automated emails etc. so a big blob of text typed in by a human won't work for contact details - especially if we expect certain info (e.g. email, phone number, address maybe)
3. Do we need to know the edition of the book?
4. Does the borrower really borrow a "book" or an instance of that book? What if the library has multiple copies of the book? How will your schema handle this without duplication of data (e.g. remaining normalised)
5. How do we know when the borrower borrowed the book so we know when it's due? Do we need to know this?
6. Do we need to know the borrower's history? (E.g. the previous books they borrowed, when they were borrowed, when they were returned)
7. Can good constraints etc. be added for 4/5/6 to ensure data integrity?
8. Will the same author write multiple books? Do we need to look the author up by their attributes or find other books by them? If so, should author also be normalised and split into its own table?
9. Does the library have an online system the borrower can log in to? It might a login name and a password if so. (Which would of course get stored hashed and with a salt)

Basically when designing a schema, you need to be mindful of what data is needed to stored and queried, normalisation (e.g. not duplicating data), performance (which is often at odds with normalisation and integrity) and integrity of the data.