In the architecture of an MVC application is it better to add a Service layer or create Database views?

Question

Assume you have a database where you store the information about a ticketing system (like helpdesk). The (simplified) schema is:

1. Ticket (TicketId(PK), TicketDesc, TicketCreated, TicketClosed, AssignedToEmployee(FK))

2. Employee (EmployeeId(PK), EmployeeName, EmployeeFunction)

Where there is a one many relationship between Ticket and Employee on AssignedToEmployee = EmployeeId.

You have to develop an MVC application (DAL composed by EF entities + Repository class) that displays graphs and statistics about tickets, such as Number of Tickets Assigned to users in a specific time period. In order to calculate the statistics and provide the values for the graph to the View, you need to perform some logic. This logic can be put in the Controller or in the Repository. Since the Controller has to be kept as slim as possible and the implementing logic within the Repository will increase coupling with the database, what is the best in this case? Create a Service layer or create database views? In the latter case, the created views are to be considered entities within my EF?

Answer 1

I'd create a service layer. Controllers are really part of View, in my opinion. Repositories should not be doing calculations. You need the Service in-between. This arrangement will have a number of advantages:

1. Services can be reused by other apps in a SOA.
2. Services can be exposed using any number of remoting techniques (SOAP, REST, XML-RPC, etc.)
3. Services are the natural owners of units of work; they should manage connections and transactions.