A little help with my database structure?

First off, sorry about the image size.

Hi guys. I was wondering if you could give me a hand in making my database structure normalized.

As you can see, I made three database designs here for a Library Management System. Ver 0.5 being the most simplest among the three, and 0.7B being the most (I think) complex.

My questions are, aside from asking for help to get my database structure normalized:

1. What can you suggest in the structure of the Patron entity. I want the Patron entity to exist in such a way that I can "combine" the Student and Faculty into a single entity just like in v0.5. The problem is, regarding the year and section of the student, and the department of the faculty member. I thought that, maybe we could simply just place those information into the Remarks field - then again, what if the user was supposed to look for a student from section XX, or a faculty member from department YY? So, I made v0.7B, separating the Student and Faculty entities and connecting them into a joint table, Patron. I think that this made it a little more complex than I had initially imagined, so I asked my professor to help me on the database design. She told me to create a table that will determine what kind of Patron that patron is (meaning, create a PatronType table and connect it to Patron table, as seen in v0.7). So, which one do you think is the best? 0.5, 0.7 or 0.7B? Or do you have a better idea in mind?

2. Is there anything wrong with the FKs in accordance with the relationships? I have to admit that it's been awhile since I last worked with Entity Relationships, so I'm not so confident. :)

If you guys have any questions regarding my topic, please feel free to ask. And thanks in advance to everyone who'll try to help. :D

Having looked at 0.7 and 0.7B, I realize that the DEPARTMENT information has gone missing. Is this information relevant to the library management system?

For Borrowed books, it would be a good idea to store the username from useraccount table. This is to help you know which librarian checked out the book for the patron.

Without knowing all requirements it would be difficult to say with certainty what the best design would be. Since you are in a better position to review design against requirements, we can offer mostly advice.

Check what transactions are handled by system, who process owners/encoders are
http://www.vbforums.com/showthread.php?t=591874 I don't see sense of BorrowedBooks table, first copy can be defined as BookCopyID = 0. And status is based on last transaction that occured for book (no nede to sync data in two tables).

@abhijit: I placed the department as part of 0.7B because the Faculty table somewhat "requires" it. It is not present in 0.7 because I thought of simply having some data coming from fields that are required by the Student and Faculty table (from 0.7B) to simply be placed under the Remarks field.

And as you can see from 0.7, I combined the Faculty and Student entities into one. The reason why I separated the two entities in 0.7B is because there are some fields that are present one table and is not present on the other (like Student, having the Year and Section fields, and Department from the Faculty table).

0.7 was a suggestion of my professor, wherein she told us to simply have the Faculty and Student entities combined as one, and have a PatronType entity instead to do the classification of the Patrons.

Quote:

---

Originally Posted by abhijit

For Borrowed books, it would be a good idea to store the username from useraccount table. This is to help you know which librarian checked out the book for the patron.

---

I think this is covered by the Transaction entity?

@leinad31: Well, this is pretty much a system from scratch. And yes, I am seeking out advices from everyone here at VBF with regard to making sure if the database design is already normalized. I'm not sure if this is the requirement that you are talking about (do correct me if I am wrong, as I am a tad confused about what kind of requirements you are requesting): we are supposed to make a Library Management System that covers Inventory Management, Transaction Processing (Borrowing and Returning of Books) and Cataloging (Searching of Books). Was just wondering if the posted database structures will be able to live to its purpose with its current state.

Quote:

---

Originally Posted by leinad31

I don't see sense of BorrowedBooks table, first copy can be defined as BookCopyID = 0. And status is based on last transaction that occured for book (no nede to sync data in two tables)

---

The purpose of the Borrowed Books table is to monitor the borrowing/returning history of a certain book. But you do have a point. The current status of a book copy is based on the last transaction that involved that particular copy of a book.

Do you think I made the design a little bit to complex? (To be specific, in the Book<->BookCopy<->BorrowedBooks<->Transaction part of the database)

Before I forget, thanks to those who replied and those who viewed this thread. I hope that more suggestions will come!

Rei

Sorry I was in a hurry earlier and wasn't able to organize my thoughts properly.

regarding BorrowedBooks, IMO a more general approach to inventory that would also take into consideration other scenarios where a book copy will inventoried out, sold, phased out, damaged, donated, etc, would be more appropriate rather than limiting to just borrowing. Include an inventory movement type field and a date field (so you know when it occured and provides additional column for analytics/filters).

As to transaction, borrowing and returning books are different transactions... case wherein penalty/fine needs to be applied is yet another transaction (case to case and involves receipt/revenue bureau requirements). You may need more than one table for this in light of different reports required as well as access requirements (stricter for those involving money, no deletions/updates allowed, correcting entries/reversals are yet another transaction). So you need to incorporate transaction type to make design more flexible. Violations would also have a history with this design.

As to patron, go for the design with minimal columns in patron table so joins to transactions and other sizable tables are performant. You don't need to know all info in a typical transaction activity (only in special cases such reminding borrower to return book will you really need to pull out complete customer info). If patron type is an important attribute then transfer to patron table to make filter/searches performant (no need to retrieve addresses etc just to check type). At the expense of additional storage from additional column, this also allows growth in information captured for patron, e.g. multiple addresses and contact numbers, without business critical queries suffering from performance penalty if it was joined to a large multi column table. Also for audit purposes, you are able to segregate updatable columns (e.g. contanct number) from those that shouldn't be changed, e.g. name.

This will really be a complex design because you have to allow room for growth... to few tables will limit transactio types that can be incorporated.

Include audit related fields where applicable, e.g. your not keeping track of when crucial info was encoded, when it was last changed and by whom.

I have a post in another thread regarding use of surrogate keys and transaction design for serialized control numbers, e.g. receipt numbers which musn't have gaps and must be sequential. Please try to search as I have to log out. Cheers.

I don't see the transaction entity relating to librarian. Am I missing something?

@abhijit: Ah, I thought you were talking about which patron checked out the book. Alright then, I'll add that up! Thanks!

Quote:

---

Originally Posted by leinad31

Include audit related fields where applicable, e.g. your not keeping track of when crucial info was encoded, when it was last changed and by whom.

---

@leinad31: Thank you for noting that, that is certainly something that I missed! As for your other suggestions, I will make sure to incorporate them as necessary on my next database design!

You also need to clarify purpose of transaction table, as it seems to focus on transaction details rather than being a master transaction table.

Quote:

---

Originally Posted by leinad31

You also need to clarify purpose of transaction table, as it seems to focus on transaction details rather than being a master transaction table.

---

Question: what would be the necessary fields in a master transaction table?

Details that appear in the receipt/invoice and has to be maintained separately per internal revenue and audit requirements (think point in time snapshot), e.g. even if patron name was changed in reference table, recorded name in transaction table must still be the same as in printout/hardcopy (unless you will maintain FK in patron table, name change results in new patron record), VAT and other rates (fines, etc) could also have changed hence historical value also needs to be stored.

Amounts, taxes (separate columns for VAT, local government tax, doc stamp tax, etc), discounts will also have to be stored. Up to you if you will implement them as additional rows in transaction details or as columns in transaction master table. It will depend on how you plan to implement the computations.

Columns for reference numbers (e.g. reference to other documents, or IDs to pertinent entities) are also needed for audit purposes.

Quote:

---

Originally Posted by leinad31

Details that appear in the receipt/invoice and has to be maintained separately per internal revenue and audit requirements (think point in time snapshot), e.g. even if patron name was changed in reference table, recorded name in transaction table must still be the same as in printout/hardcopy (unless you will maintain FK in patron table, name change results in new patron record), VAT and other rates (fines, etc) could also have changed hence historical value also needs to be stored.

Amounts, taxes (separate columns for VAT, local government tax, doc stamp tax, etc), discounts will also have to be stored. Up to you if you will implement them as additional rows in transaction details or as columns in transaction master table. It will depend on how you plan to implement the computations.

Columns for reference numbers (e.g. reference to other documents, or IDs to pertinent entities) are also needed for audit purposes.

---

I don't think that there really is a need for the taxes and discounts part. I mean, the database that I'm working on is simply a database of a small public high school. Taking that into consideration, is there still a need to create table for internal revenues, VAT, etc. ?