My design for my database is almost perfect but for one problem:
http://i306.photobucket.com/albums/nn269/MCTWEED15/newDatabase.jpg
As you can see - a copy of a book needs a location. However the requirement says
if it is a short loan then the book will not be found on the shelves/rows. This means, with the current structure I will have NULL values for Row and Shelf if ShortLoan is true
How can I change my design to prevent this?
thanks
I think ShortLoan should be a property of BookCopy (or perhaps even Book) rather than Location. This would mean that the LocationID on BookCopy would be optional. It is very hard to design a data model that has no NULL-able columns.
edit
"okay-ness" is a hard thing to measure when it comes to data models, and doubly so when we don't know the business requirements. I don't understand the rules which underlie the ShortLoan flag so I can't really advise you. But it seems to me that that a library which acquires several copies of a hot book (the new Harry Potter) might mark a couple of copies ShortLoan so that more borrowers could have the chance to read it, but not all copies, so as to give the slow readers enough time to finish it.
The other issue is this: if the setting of the ShortLoan flag determines whether the BookCopy.LocationID flag can be set then it is better if the flag is on the that table, because that arrangement allows us to enforce a check constraint which we cannot do when the columns are on two separate tables. Physical considerations shoudl not drive the Logical Data Model but they do apply once we start thinking in terms of tables rather than entities.
edit 2
I thought ShortLoan meant the book could only be lent for a restricted period (e.g. three days rather than the standard three weeks) whereas Loan is an actual transaction. So in my understanding the BookCopy.ShortLoan flag would determine the Loan.ReturnDate but it is not of itself a property of Loan.
This is why most data modelling projects usually end up in a heated discussion around a white board!
