StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow should I design my model/viewmodel for this application?
text
Body
copied!<p>I'm working on a web application using ASP.Net-MVC3 on II6 with SQL Server 2008. I can't give details about the project so let's say for the purposes of this question it's for a library. There are Books and people can Check Out these Books. We want to keep a record of past check outs CheckOuts. So here's the data schema:</p> <pre><code>Books Checkouts ------------------- ---------------------------- | ID (PK) | | ID(PK) | | Title varchar(50)| | BookId (FK Books.ID) | | | | CheckoutDate (DateTime) | | ... | | ExpirationDate (DateTime)| | | | Username (varchar(50) | | | | ... | ------------------- ---------------------------- </code></pre> <p>The idea is that the relationship between Check Outs and Books is many-to-one, but only one CheckOut will ever be "Active" or "Not Expired", i.e. at most one CheckOut per book will have an expiration date greater than the current time. The application will enforce this.</p> <p>My first question is: Do you think this is a good database design for the requirements mentioned? Or should I maybe include an "Expired" boolean column to the Check Outs table? Other ideas?</p> <p>Our models for MVC look like this:</p> <pre><code>public class Book { public int ID { get; set; } public string Title{ get; set; } ... } public class Checkout { public int ID { get; set; } public int BookID { get; set; } public sting Username { get; set; } public DateTime CheckOutDate{ get; set; } public DateTime ExpirationDate { get; set; } ... } </code></pre> <p>For our data access layer we are just using System.Data classes such as SqlCommand and SqlDataReader, so there is none of the benefits of EF, LinqToSql, etc such as Relationships and Lazy Loading.</p> <p>Now my problem is that we want to search these books and display a table something like this:</p> <pre><code>Title Checked Out Checked Out By Check Out Expiration -------- ----------- -------------- ---------------------- Book1 Yes Username 01/01/1970 </code></pre> <p>The query would to get this info would look like</p> <pre><code>SELECT * FROM Books LEFT JOIN Checkouts ON Checkouts.BookID = Book.Id WHERE Checkouts.ExpirationDate <= GETDATE() </code></pre> <p>How would I do this?</p> <p>Would I make a model like this</p> <pre><code>public class BookSearchTableRow { public string Title { get; set;} public bool CheckedOut { get; set;} public string CheckedOutBy { get; set;} public DateTime CheckOutExpiration { get; set;} } </code></pre> <p>and make my View take a List ?</p> <p>Or should I add a CheckOut property to my my Book model?</p> <pre><code>public class Book { public int ID { get; set; } public string Title{ get; set; } ... Other Book Fields ... public Checkout ActiveCheckout { get; set; } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload