I have a TSQL view. Apart from a few columns it is pretty basic in that it simply does a few joins and then glues everything together to present a nice view as it should be. However, the few columns which are not so simple makes the view code very hard to extend, now that new requirements have come in that invalidates the complicated columns' business logic.
Without going too much into detail, there is a table in my database:
tblEmployment
This consists of rows of "employments". Every time any of the columns in a row, for a given employment changes (let's say the employmentTitle changes), then the current row is pushed into another table tblEmploymentHistory, and the row in tblEmployment is changed so it contains the newest employmentTitle.
Essentially, what the view does, is that it tries to join the tblEmployment on tblEmploymentHistory with the unique EmploymentIdentifier, which makes sense.
The more complicated column in the view will try to calculate a number (elapsedTime for each row) by doing various calculations from the rows that it has joined together (i.e. from tblEmployment and tblEmploymentHistory). To get the elapsed time it performs calculations based on dictated business logic e.g. only specific datetime columns in the history table should count towards the total elapsedTime and it should only do so if other columns in that row is set to specific values etc.
Now that new requirements have come in, the business logic is much more complicated than before. I find it hard to extend the view to include this since it's getting very messy and I feel that this could be done much more structured in the application layer where the rest of business logic also resides!
Is it "correct" to scrap the view and instead move it to the application layer of my application? Obviously the benefit by having the view is that it's fast, and doing the calculations in the code for roughly 100.000 rows will take some time. However, it can be optimised by filtering away rows to make the number around 10.000.
What is the "standard" and the cleanest way of tackling this problem?
The answer depends on a few things. First, make sure that the database is doing set-based work. If you start getting into cursors (generally speaking) or some sort of loop, you'd be better off placing that in the application. Relational databases aren't efficient working in that manner. Another thing I'd consider is what is standard for the environment you're working in? Are other things maintained in the DB like this where you are? If so you may want to stay consistent.
In the end whatever returns those results most efficiently, and without affecting other queries, ought to be the answer.