- Refactor Database
- Add constraints so that the web application does not break. An example was when I added a new contact, but left the keyword_1 field blank. This broke the external application in a frustratingly subtle way and nobody could create new proposals for a week and a half until I discovered the subtle bug. [3-5 days to go through all 118 tables]
- Combine multiple user tables (contact_list table and project_users table) into a single user table. I would like to verify this is a good idea with Chris first. [1-3 days]
- Add a roles table so that a user can have more than one role [1 day]
- Add status, facility, role, agreement_status, etc child lookup tables so that these values are not hard-coded in the parent tables (and the text can be changed in one place) [2-3 days]
- Change the parent-child relationship between a proposal and a user so that if a user is member of more than one proposal, that user has only one entry in the user table. The current user table has instances where a user is a member of six proposals, so there are six different users with the same name. I would like to verify this is a good idea with Chris first. [2 days]
- Figure out a way to clean up duplicated names, unfortunately I noticed that some dupes have different last names due to getting married. Other instances may have same name but different emails, variations of last name, sometimes including middle name, mistypes, etc. Again, talk with Chris if historical data (old names or old contact info) is preferred. [2-3 Days]
- Clean up the duplicated names in contact_list table because the user needed more than one role and this is the only way to support multiple roles. [1 day]
- Look at the proposal_stage and proposal_status tables – should this be a combined in a single table, keep as two tables, or more break apart into more than two tables? Will need to talk with UserOffice, Chris, and preferably other NSRC's to make this completely generic. The current proposal_stages is somewhat rigid and specific to the Molecular Foundry [5-10 Days]
- Lot’s of empty tables (publication table, proposal_reports, etc) – should we get these features working? [3-5 days]
- Update hundreds of stored procedures [1 - 2 months]. Note, I may not rewrite stored procedures that generate lists and instead create a single, generic view that could replace a large number of existing strored procs, see section 3, part 1 below. Will still need to modify the write stored procs.
- Update External Website to use refactored database
- Once the database is updated, the external website will break. Database access code needs to be updated – most of the pages read or write from the database. I will likely add a data access layer to clean up the code-behind and *might* consider adding a business layer, but only if this will be faster. The goal is to do the minimum about of work necessary to keep this application running on the new database schema. [2-4 weeks]
- Refactor Internal Website
- Remove refactored code. When making a bug fix or feature enhancement to the internal website, I generally edit multiple files to make the fix. Example – to add an additional column to a grid, I will go separate files to update the UserOffice, ScientificDirector, PSP, LeadScientist, etc. I propose combining the code into one file and have a role table control features that depend on the role.
Additionally I would like to rewrite the data access code to be more efficient. A typical example is when a list is generated. If each row needs to get data from 12 related tables, then the code makes 12 database queries. If the list has 500 rows then there are 12 x 500 = 6,000 database calls to display one web page. I would like to rewrite all these pages to make one database call using INNER JOIN syntax. This is complete for the all submitted page, but none others. [2-4 months]
- Replace the iFrame architecture with MasterPages – not that big a deal but the current iFrame design makes using Ajax and debugging in Firebug either difficult and sometimes simply not possible. [this will happen as part of doing (a)]
- Add business object layer, example would be adding a proposal object, a user object, a survey object, etc. Not a big deal but object oriented design and coding is considered best practice. [this will happen as part of doing (a)]
- Add a data access layer. Most data access currently occurs in the web page. Not a big deal but is considered best practice to separate database access from the web page. [this will happen as part of doing (a)]
- Write data migration tool – the new database schema is incompatible with the old schema. Data will need to be migrated just before switching to the new internal and external systems. [1-3 weeks]
- We propose to continuously QC the product as it is being developed, preferably using a dedicated team of personnel both internal and external to IT/TMF.
- We expect to conduct extensive comparison, validation and stress testing on the finished product prior to its full production release [2-4 months]
- Bug Fixes
- Assuming there are no bugs or issues during testing this would be 0 days. We highly doubt there will be no issues, but they should be minimized by rigorous routines in the QC/QA phases [1 week - a few weeks?]
* This estimate assumes there will be no unexpected surprises. It is common business practice for management to take programmers estimates, which are generally optimistic anyway, and multiplying by 2x or 3x.
** Is it possible to make these changes on a page-by-page basis and refactor the database in small steps. Changing the web pages one page at a time is certainly possible. Changing the database schema in small steps is also possible but will need corresponding changes in both web applications to keep them from breaking.