#database views

View On WordPress

# Problem # There is a need to implement major functionality, such as a ticketing system, into an existing environment. Any new functions will need to rely on users, groups and other common information already maintained and administered in a centralized database. Oh yeah, and its needed "ASAP." # What To Do? # At this point most developers are faced with either building a custom solution or leveraging an existing product. * Build a solution from scratch * Guaranteed to work with existing infrastructure * Lots of time and internal cost / support * Use an existing open source or off the shelf solution * Proven solution * Community/Vendor support * Will usually have more features right away than a custom solution In my specific case, time and resources did not exist to build a solution in-house. An existing product would need to be integrated into the environment. # How To Integrate # ## Modify The Code ## Because the product we chose was open source, my first thought was to dig though the code and modify queries, objects and views to bypass parts of the product's database. I quickly realized that many of the changes would alter the core of the product. To maintain all the necessary changes would be cumbersome as patches and upgrades were released. ## Data Sync ## Next I looked into writing a script that would synchronize data from our existing environment to the product's tables. Time and effort to implement were negligible compared to modifying the code but it would be difficult to produce "real time" data without creating database triggers or intercepting SQL commands. Data in the product's database would always be lagging behind the existing environment. While there is no shortage of ways to keep data synchronized between tables, almost all solutions will require extra code to be maintained and monitored to ensure the process is running smoothly. ## The Magic Of DB Views ## The following solution nearly eliminates code changes, is real time and requires minimal upkeep over the life of the product. Install the product's database tables on the same server as the existing centralized database but under a different schema (or use table prefixes if available). Next create a list of all common data points between the existing environment and the new product. Once the data points are mapped remove the product's original tables and replace them with views. To keep things simple I will focus on users. Move the product's users table to a different name and remove any foreign key constraints other tables might use. Then create a view within the product's schema that has the same name as the original table. The view will query previously existing tables and provide that data with the same column names the product shipped with. Since the view has the same name as the original table all the queries in the product's code will work without modification. Pseudocode Example: product.users id username active manager_id attr_A arrt_B existing.users user_id username manager_user_id terminated_date attr_A attr_C alter table product.users rename to product.Z_users alter table product.TABLE_WITH_FK_TO_USERS drop constraint FK_TO_USERS create view product.users AS select existing.users.user_id AS id, existing.users.username, existing.users.manager_user_id AS manager_id, convert_to_boolean(existing.users.terminated_date) AS active existing.users.attr_A, 'default value' as attr_B from existing.users ## Gotchas ## * This will not always work out to be a favorable 1 to 1 table for view substitution but a view can probably get most of the work done even if a few tables need to be joined together. * Removing the foreign keys could be a hit to data integrity. Thorough tests will reflect how big of a hit, if any, the application will suffer. * Inserts/Updates from the product could fail. Some DB Views will pass inserts/updates straight though depending on how the view is created. If this is not the case you may still have to dive into the code to stop writes from happening. * Some columns just won't map. If there is a column that is absolutely needed but not in an existing database table, add it. Maintain data though the system you already have in place and force the new product to be a consumer of common resources.