How should I design my model/viewmodel for this application?

Question

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:

Books                   Checkouts
-------------------     ----------------------------
| ID   (PK)        |    | ID(PK)                   |
| Title varchar(50)|    | BookId (FK Books.ID)     |
|                  |    | CheckoutDate (DateTime)  |
|  ...             |    | ExpirationDate (DateTime)|
|                  |    | Username (varchar(50)    |
|                  |    | ...                      |
-------------------     ----------------------------

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.

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?

Our models for MVC look like this:

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; }

    ...

}

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.

Now my problem is that we want to search these books and display a table something like this:

Title     Checked Out  Checked Out By  Check Out Expiration
--------  -----------  --------------  ----------------------
Book1          Yes         Username        01/01/1970

The query would to get this info would look like

SELECT *
FROM Books
LEFT JOIN Checkouts ON Checkouts.BookID = Book.Id
WHERE Checkouts.ExpirationDate <= GETDATE()

How would I do this?

Would I make a model like this

public class BookSearchTableRow {
    public string Title { get; set;}
    public bool CheckedOut { get; set;}
    public string CheckedOutBy { get; set;}
    public DateTime CheckOutExpiration { get; set;}
}

and make my View take a List ?

Or should I add a CheckOut property to my my Book model?

public class Book {
    public int ID { get; set; }
    public string Title{ get; set; }

    ... Other Book Fields ...

    public Checkout ActiveCheckout { get; set; }
}

Why do you even need a relational database? Why not store it in a schemaless database like RavenDB? — George Stocker, May 27 '12 at 03:10
Not an option. Database and DataAccess are already in place. It's not actually for a library. It's just the most similar example I could come up with, without giving too many specifics. — Manual5355, May 27 '12 at 05:02
Now I'm really confused. If the database is already in place, then why the heck are you asking us if this is a good schema? If you can't change something that doesn't make sense, there's not much we can do to help you. — George Stocker, May 28 '12 at 01:44
Database is in place, SQL server 2008. I can't change that, but the schema can change. Database access is in place. We use SqlCommands and SqlDataReaders. This isn't able to change (at least in the short run) to anything like EF, Linq2Sql, etc. I'm just asking if having a CheckOuts table and a Books table, with a many to one relationship, makes the most sense for what I described. i.e. You can check out books, and there should be a record of past checkouts. I will edit the question and add more to that part of the question. The major question here though is how I should design my viewmodel. — Manual5355, May 28 '12 at 14:30

score 0 · Accepted Answer · answered May 28 '12 at 14:58

0

How you design your viewmodels shouldn't influence the way you design your models and vice versa. Your models usually represent your domain and your viewmodels are (usually) transformations of these models suited for displaying specific parts.

If I were you, I'd send an IEnumerable of something like the BookSearchTableRow class you mentioned to your view. That resembles what you want to display. If your models represent your domain correctly, you shouldn't modify them because of something a view needs. That's what viewmodels are for.

answered May 28 '12 at 14:58

Kristof Claes

10,797
3
30
42

I was leaning towards that approach. One final question though. If I go this route, and say I create a BookSearchTableRow class. I can see it very difficult or inefficient to project the search results (a List) for this programmatically (i.e outside of the database). It would involve then calling the database again to see if it was checked out. Do you think it's kosher (in terms of separation of concerns) to have my data access layer to have a method that returns a List? – Manual5355 May 28 '12 at 15:52
1

I'd say not. Your data access layer should have no knowledge of views or viewmodels. – Kristof Claes May 28 '12 at 19:14
How would you recommend doing it then. It seems to me that either a) the DataAccess layer returns a List, which takes a single database call and N iterations to map the results to Domain Models, or b) it returns a List and then I have to make another call for each book to get it's active checkout, and then finally map all that data into a List. That's N+1 database calls and something like 3N+1 iterations to get N BookSearchTableRows. Another option would be for my database access to return some aggregate like a List> – Manual5355 May 29 '12 at 00:58
If you are needing this kind of aggregate (books with checkout data/status) in a lot of places you can always provide something in your data access layer that returns an object that resembles this. This object isn't necessarily a projection of a view though. Another option _could_ be to change your database schema and add the current status of a book to the Books table: Books ------ ID, int (PK) Title, nvarchar(50) CheckoutDate, datetime, nullable ExpirationDate, datetime, nullable Username, nvarchar(50), nullable You can then only store the _previous_checkouts in table Checkouts. – Kristof Claes May 29 '12 at 07:05

How should I design my model/viewmodel for this application?

1 Answers1