Strategies to overcome the evolution of the circuit?

Question

Currently, we use a manual SQL package in Data-Access objects and many stored procedures and triggers, which comprise about 20 thousand lines of code. We find that simple changes cause work to be fixed in a couple of days, and its deadlines for slippage.

Changes include changes to tables to cope with additional data, general refactoring of the schema based on QA / user reports, etc. Its a very active system that is created to replace something old and slow.

We looked at the available PHP ORM solutions to try to limit the effects of these changes, but they were too slow to handle our scheme; The "simple" sql results were an order of magnitude longer than our user queries and caused ~ .5s page views to take longer than 20 seconds.

What best practices / strategies could I learn to deal with the evolution of a schema with relational databases in a general context?

Edit: forgot to mention triggers; we have a lot of data that are based on cascading changes, for example. changing the price here for this user updates the price there for this user, etc.

Answer 1

You might want to check out this book on Database Refactoring: An Evolutionary Database Design .

Answer 2

I suggest using a continuous (or at least nightly) build strategy.
Rebuild the database on each control, or at least once a day.
Also, run unit tests once a day to execute each bit of code, whether in a stored procedure, trigger, or data access slot.

There is a high cost to writing stored procedures, but this will immediately reveal interruptions.
Once you know where the break is, you can fix it.

I would be interested to hear the experience of other people in this strategy applied to database changes.

Answer 3

We use Enterprise Architect for our database definitions. We include stored procedures, triggers, and all table definitions defined in UML. Three brilliant features of the program:

• Import UML diagrams from an ODBC connection.
• Generate SQL scripts (DDL) for the entire database at once
• Creating custom template documentation for your database.

I have never been impressed with any other tool in 10 years as a developer. EA supports Oracle, MySQL, SQL Server (multiple versions), PostGreSQL, Interbase, DB2, and Access in one fell swoop. Every time I had problems, their forums quickly answered my problems. Highly recommended!

When database changes occur, we do this in EA, generate SQL and check it for our version control (svn). We use Hudson to build, and it automatically creates a database from scripts when it sees that you modified the registered sql.

Answer 4

My advice would be to get rid of stored procedures and instead use the embedded SQL, possibly supported in text / xml files. I find SProcs to be much more annoying and time consuming to maintain. After you create a query plan (the first time you run a query), you will notice a slight difference in performance. In addition, you can control all your DB scripts ...

Answer 5

Here are my suggestions:

• Try to get rid of the least used features. Set functions that are not used all the time. Each application function has several levels of costs associated with it (support, support, regression testing, code complexity, etc.).
• Stay away from stored procedures if there is absolutely no way to do this efficiently and scalably in your code.
• Add an ORM solution gradually (using refactoring to migrate from JDBC to ORM) to reduce code and code complexity in CRUD transactions.
• Build functional, integration, and unit tests when you fix a bug and incorporate these tests into a continuous integration system. Automate regression testing as much as possible to identify problems as soon as they are introduced during registration.
• In general, whenever you fix a bug, use this opportunity to reorganize to decouple the implementation modules / code.

If you have questions about database migration issues, this may help: http://shashivelur.com/blog/2008/07/hibernate-db-migration/