February 16, 2016 at 12:15 pm
So, need a little help with coming up with some ideas for the best way to do the following:
We have a very convoluted, band aided, complex system that is getting redesigned. The database is super messy, has no primary/foreign keys, is not normalized, has duplicative data and objects etc... the goal is to redesign the system piece by piece.
So, 1) redesign the data model and 2) run the old system and new system parallel to each other while moving over pieces of functionality at a time. But yet maintain seamless appearance in the front end.
What is the best way to handle this in the database? We were thinking to switch the system to using the new data model but then create views and other supporting objects to mimic the old functionality so that the front end code wouldn't have to be changed (a lot). Personally, I think this method is TONS of work and requires a lot of backwards momentum. However, I could be wrong and this is the best solution.
A second solution would be to have both databases stood up and only use the new database for the pieces that have been migrated and continue using the old database for "old" functionality. This seems problematic to me because the front end code would need to be changed to say if this go here if that go there and seems like a pain to integrate both data sets (which will be totally different) to make sure reporting and searching is not negatively impacted...
Are there any other options? Has anyone ever gone through this before and found pros/cons to each method? Even if one method isn't the best, I need to have pros and cons of each.
Thanks in advance!
Please let me know if I didn't provide enough information.
February 16, 2016 at 12:23 pm
I hate to say this but it "depends" . How is the database served to the application. Is there a single database for each client or all clients data are shared in the same database?
February 16, 2016 at 12:28 pm
This is a web based application with a ColdFusion front end. There is 1 database. Users log into the system via the front end and all actions are recorded in the single database.
February 16, 2016 at 1:00 pm
In most cases I would advocate the "new design with views that mimic the old mess" approach.
It may be more work, but you really reduce the risk significantly. And you can do a piecemeal approach instead of having to build a full replacement and then big-banging it into production.
Another benefit is that if you test the views compeletely, you can get away with limited testing effort on the rest of the application. After all, they never see the change.
(Note: Depending on the complexity of the views, it may be needed to write instead of triggers for them. That is not everyone's cup of tea).
February 16, 2016 at 1:17 pm
Thank you for the response, can you explain the "instead of" triggers you mentioned? How exactly would that work?
February 16, 2016 at 1:22 pm
What exactly is the application using the database for? Is this a data warehouse/reporting system or is it processing transactions?
If it's a reporting system then views could be a perfect solution that should have minimal impact on the application.
On the other hand if it's being used for transaction processing by the application how is the application managing that now? Is it hitting the tables directly or does it go through stored procedures? If it's going through stored procedures that should also be possible to do with minimal impact on the application, if it's hitting the tables directly though you could be out of luck.
February 16, 2016 at 1:25 pm
If you create a view, it is legal syntax to make that view the target of an insert, update, or delete statement. By default, SQL Server will attempt to reverse engineer the definition of the view to make the corresponding changes in the underlying table. But that logic is fairly limited, and it also only works if the corresponding change is in a single table only. So as soon as the views move beyond a fairly basic level of complexity, the builtin handling of insert, update, or delete on the view will fail.
For your project I would assume that the frontend code that does the modification will be very early in the timeline of redesigning and rebuilding, but you may still have some time where the new architecture is in place (with views for old-mess-compatibility), and some programs still target those old objects for modifications. That's where the INSTEAD OF trigger comes into place. You can define this trigger on a view. Now, when someone does an insert, update, or delete into the view, SQL Server does no modifications to the base table at all; instead it only invokes the trigger telling it the changes the query made (in the inserted and/or deleted pseudo-tables). The code in the trigger is then responsible for modifying the underlying base tables. Or for doing something completely else if you feel like it, but that would not help you in this case.
Basic explanation and very sime example: https://msdn.microsoft.com/en-us/library/def01zh2.aspx
February 17, 2016 at 7:57 am
It is a transactional system with reporting capabilities. The front end code updates tables directly most of the time. There are some instances where it calls stored procedures but for the most part, it is hitting tables directly. Doing a variety of inserts, updates etc... some of the queries require joins to 5+ tables.
Thank you for the information for the instead of triggers... that makes sense.
February 18, 2016 at 2:04 pm
amy26 (2/17/2016)
It is a transactional system with reporting capabilities. The front end code updates tables directly most of the time. There are some instances where it calls stored procedures but for the most part, it is hitting tables directly. Doing a variety of inserts, updates etc... some of the queries require joins to 5+ tables.Thank you for the information for the instead of triggers... that makes sense.
From what you just stated, part of the information for a table could be broken out into another new table (following normalisation rules). So in this case there has to be refactoring of code likely in the application since you mention in mostly writes to tables directly. I would take the opportunity to have the application call upon stored procedures when re-engineering , rather than writing code directly within.
----------------------------------------------------
February 22, 2016 at 9:34 am
This is a perfect example of why you should have an N-tier approach to the application development and MVC lends itself to this. MVC is a generic pattern but also a .NET methodology which extends the MVC principles to make development faster by providing conventions and auto-complete in Visual studio. You don't need to be developing in .NET to employ the MVC pattern, Ruby and PHP both have MVC frameworks available and I am sure that other languages will also have them
MVC stands for Model-View-Controller.
In a perfect world you would have
Front end UI that consumes View-models: The Front end UI knows nothing about how the View-model is built. Everything the UI needs in terms of data is in the view-model
View-Model: this is build from the Domain-Models to complete whatever task is required by the UI. It is generally good practice to have one View-Model per UI process
Domain-Model: These are class definitions that represent the business entities that are being manipulated. In .NET where the domains are related then one class contains a reference to another; one a 1:many relationship the parent contains a collection of children. Domain models are made up of primitive data types [like you find in the database] and more complex data that is derived from the data in the primitives (e.g. FullName is a derived value that is made up of Title, firstName, Surname)
Data repository: this is your database. In many cases the data tables will look very similar to the domain-model primitives. ONLY Domain-model classes should be able to update the database (via the data access layer). The data repository should still have primary and foreign key constraints and use the proper data types and (preferably) be 3NF for a transactional system.
Controllers: The Controller sits at the heart of the application and determines what actions should be taken for a particular requested Action. They are responsible for getting the data from the domain-model and manipulating it into the view-model and then deciding which view Front end UI should be called. Where there is data changing activity coming BACK from the view (e.g. POST submission) the the controller is responsible for deconstructing the view-model back to the relevant domain-model classes - but it is not responsible for updating the database.
Business Access Layer: This is where you put all the business rules which will be called by the view-model and / or the domain-models
Data Access Layer: This defines how the data repository relates to the domain-models
This seems like a lot of work (and it is!) but it means that any part of the system should be hot-swappable without the whole thing breaking. It means that you can transform the database and simply update the Data Access Layer rules and the Domain-models will not break. If you need to remodel the Domain-model, as long as the View-model is unchanged the front end does not need to be reworked. If you want to build prototype features in a noSQL database you can do as the Data Access Layer will point to the noSQL database and then you can convert it when you are ready to productionise the prototype
good Luck
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply