Look or “View” Before You Leap

By Mike Wood | Published March 14, 2011 | Categories: Cascade CMS

I think a good habit you can get into as a developer is to think about several ways to tackle a task instead of implementing the first solution that pops into your head.

For example, a client recently decided they wanted to duplicate one of their sites.  So we copied the code and the database. (The site’s data is stored in a Sql Server database.)  Part of the database contains data used for reporting purposes. The new users have access to this data but will not be updating the data. At first glance, if we used the copied database as is, we would need to duplicate the process that populates the reporting tables. However, we don’t want to go that route.  In addition to storing two copies of the same data, changes to the process would require coding the same updates in two different places.

The next idea was to change the all the database calls that reference the reporting tables to access the data from the original database.  This would work but would require changing a lot of calls in several stored procedures that are used to pull the reporting data from the database.

How do we access the data without changing any of the select statements wherever they may be? We then set up a view in the same name as each of the tables from which we wanted to access data from the original database.

Say we have a table named "products" in the original database.  We didn’t include that table in our new database. Within the code on the new site, the select statement might say something like SELECT name, color, weight FROM product.  If we changed the select to pull from the original database, the select statement would look like SELECT name, color, weight from originaldatabasename...product.  Instead of doing this, define a view named Product. This view contains the select from the original table. Now any Select statement that needs data from the original table has access to the data in the table via the view.

This saves time now and in the future in that we don’t have to change all the Select statements to access the Original database’s tables.