Hi Friends, Today we are going to discuss about database extensibility and how this can be fit in business scenario for agile development.
What is Database Extensibility?
Database extensibility is the factor that how easy a database can adopt new changes without any major schema refactor or side effects.
If database schema is created in such a way that a small change in any table is causing you major changes in stored procedures, functions and client code to get data then this can be considered as a poor database design.
Database objects like tables, stored procedures, functions, views, triggers etc. should be written in such a way that it should be able to adopt new changes very easy with minimum or no code changes.
There are few guidelines to make database more extensible.
- Tables should be normalized properly.
- Generalization should be there to extend specific domain entities.
- Data duplicity should not be there in multiple tables.
- One table should hold specific domain entity requirement.
- Do not use a table to hold non relevant data with various nulls in columns.
What is Agile Development Methodology?
Being developers we very well aware of waterfall method. Waterfall is a development methodology in which we have a major part of a project goes sequential. Following are the steps:
- We do requirement gathering of a major project or major part of any project.
- We prepare designs, get approvals.
- Code the entire solution in one go.
These stages are sequential stages but the main problem is volume of work. However this methodology work fine but when it comes to larger projects then each stage takes significant amount of time and efforts. And when we achieve final implementation, till that time there could happen many business\requirement changes. These changes can create issues or again initiate a new waterfall process to tackle those changes.
In waterfall model, one has to wait till the completion of final code and product is visible.
To cater this issue, agile methodology came into picture. This is iterative approach having 15\21\30 days of sprints. Each sprint deliver a working code which can be demonstrated to client and any requirement changes can be incorporated at early stage of development.
This way one can save lots of rework and client\team can see work in progress and validate it at the same time against requirements.
Why we need database extensibility?
As we know that agile works through small iterations. As we cycle through iterations, an extensible database absorbs new business requirements with less refactoring.
If database design is poor then in further stages\iteration it will be messy to get everything fit into the database. So one should make decision at the earlier stages of designing and make database as much extensible.
Benefit of good schema
There are several benefits of good database design. Below are few.
Set Based Queries
SQL Server is designed to handle data in sets. SQL is a declarative language e.g. query describes the problem, and the Query Optimizer generates an execution plan to resolve the problem as a set.
Iterative T-SQL code is code that acts upon data one row at a time instead of as a set. The implementation generally happens via cursors. This forces the database engine to perform thousands of wasteful single-row operations, instead of handling the problem in one larger, more efficient set.
This is why set-based queries, based on an obvious physical schema, are so critical to database performance.
A sound indexing strategy identifies a handful of queries that represent 90 percent of the workload. With judicious use of clustered indexes and covering indexes, solves the queries without expensive lookup operations.
An elegant physical schema, well-written set-based queries, and excellent indexing reduce transaction duration. And this implicitly improves concurrency and sets up the database for scalability.
A database with an excellent physical schema, well-written set-based queries, and the right set of indexes will have tight transactions hence perform well with multiple users.
When a poorly designed database displays symptoms of locking and blocking issues, transaction isolation level tuning only partially alleviates the problem. The sources of the concurrency issue are the long transactions and additional workload caused by the poor database schema. Concurrency tuning cannot overcome the deficiencies of a poor database design.
The database component is the principle factor determining the overall monetary cost of the database. A well-designed database minimizes hardware costs. It simplifies data access code and maintenance jobs. It significantly lowers both the initial and the total cost of the database system.
Lower maintenance cost leads to scale systems easily.
You can read more about database design principals here.
We can see that a good database schema is the back bone of overall system. It can give countless direct\indirect benefits. While a poor designed database can create issues unexpectedly.