Results 1 to 12 of 12

Thread: A little help with my database structure?

  1. #1

    Thread Starter
    Addicted Member riechan's Avatar
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    254

    Post 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.
    Attached Images Attached Images    
    ====================
    ほんとにどもありがとう!

    Rie Ishida

  2. #2
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    Re: A little help with my database structure?

    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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  3. #3
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: A little help with my database structure?

    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).

  4. #4

    Thread Starter
    Addicted Member riechan's Avatar
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    254

    Re: A little help with my database structure?

    @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
    ====================
    ほんとにどもありがとう!

    Rie Ishida

  5. #5
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: A little help with my database structure?

    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.
    Last edited by leinad31; Nov 20th, 2009 at 08:00 AM.

  6. #6
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: A little help with my database structure?

    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.
    Last edited by leinad31; Nov 20th, 2009 at 08:07 AM.

  7. #7
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    Re: A little help with my database structure?

    I don't see the transaction entity relating to librarian. Am I missing something?
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  8. #8

    Thread Starter
    Addicted Member riechan's Avatar
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    254

    Re: A little help with my database structure?

    @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!
    ====================
    ほんとにどもありがとう!

    Rie Ishida

  9. #9
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: A little help with my database structure?

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

  10. #10

    Thread Starter
    Addicted Member riechan's Avatar
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    254

    Re: A little help with my database structure?

    Quote Originally Posted by leinad31 View Post
    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?
    ====================
    ほんとにどもありがとう!

    Rie Ishida

  11. #11
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: A little help with my database structure?

    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.
    Last edited by leinad31; Nov 23rd, 2009 at 09:46 PM.

  12. #12

    Thread Starter
    Addicted Member riechan's Avatar
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    254

    Re: A little help with my database structure?

    Quote Originally Posted by leinad31 View Post
    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. ?
    ====================
    ほんとにどもありがとう!

    Rie Ishida

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width